Related questions

Postgres lag/lead Function filter

2022-05-04 10:13 user2210516 imported from Stackoverflow

Can someone help me with the last step of my Query.

I have this table fiddle

    CREATE TABLE rent(id integer,start_date date, end_date date,objekt_id integer,person_id integer); 
INSERT INTO rent VALUES

(1,  '2011-10-01','2015-10-31',5156,18268),
(2,  '2015-11-01','2018-04-30',5156,18268),
(3,  '2018-05-01','2021-03-31',5156,18269),
(4,  '2021-04-01','2021-05-15',5156,null),
(5,  '2021-05-16','2100-01-01',5156,18270),
(6,  '2021-03-14','2021-05-15',5160,18270),
(7,  '2021-05-16','2100-01-01',5160,18271);

With lag and lead i want two columns for last person_id and next person_id.

With this Query i almost solved my Problem but there is still one thing i need help to change.

with tbl as (
SELECT rent.*,
row_number() over (PARTITION BY objekt_id) as row_id
    FROM rent
    ORDER BY id)
SELECT r.id,
       r.start_date,
       r.end_date,
       r.objekt_id,
       r.person_id,
       lag(person_id) over (PARTITION BY objekt_id, person_id IS NOT NULL AND objekt_id IS NOT NULL ORDER BY id) as last_person,
       lead(person_id) over (PARTITION BY objekt_id, person_id IS NOT NULL AND objekt_id IS NOT NULL ORDER BY id) as next_person
    FROM tbl r
order by 1;

Last or Next Person_id always have to either null or from another person_id.

At the moment row 2 will give me last_person_id = 18268 since row 1 had the same person_id. If person_id is empty i also want to see last and next person.

Output now:

id  start_date  end_date    objekt_id   person_id   last_person next_person
  1 2011-10-01  2015-10-31  5156        18268                    18268
  2 2015-11-01  2018-04-30  5156        18268       18268        18269
  3 2018-05-01  2021-03-31  5156        18269       18268        18270
  4 2021-04-01  2021-05-15  5156            
  5 2021-05-16  2100-01-01  5156        18270       18269   
  6 2021-03-14  2021-05-15  5160        18270                    18271
  7 2021-05-16  2100-01-01  5160        18271       18270

Wished Output:

id  start_date  end_date    objekt_id   person_id   last_person next_person
  1 2011-10-01  2015-10-31  5156        18268                    18269
  2 2015-11-01  2018-04-30  5156        18268                    18269
  3 2018-05-01  2021-03-31  5156        18269       18268        18270
  4 2021-04-01  2021-05-15  5156                    18269        18270
  5 2021-05-16  2100-01-01  5156        18270       18269   
  6 2021-03-14  2021-05-15  5160        18270                    18271
  7 2021-05-16  2100-01-01  5160        18271       18270

The goal with query is to choose a specific date and to tell if the object is for rent or not and then also show who rent's it at and who was the last one and is there someone in line to rent

2 answers

  • answered 2022-05-04 11:28 D-Shih

    You can try to use correlated-subquery to make it by your logic condition.

    with tbl as (
SELECT rent.*,
row_number() over (PARTITION BY objekt_id) as row_id
    FROM rent
    ORDER BY id)
SELECT r.id,
       r.start_date,
       r.end_date,
       r.objekt_id,
       r.person_id,
       ( SELECT t1.person_id
         FROM tbl t1
         WHERE t1.objekt_id = r.objekt_id
         AND t1.id < r.id
         AND (t1.person_id <> r.person_id OR r.person_id  IS NULL)
         AND t1.person_id IS NOT NULL
         ORDER BY t1.id desc
         LIMIT 1) last_person,
       (SELECT t1.person_id
         FROM tbl t1
         WHERE t1.objekt_id = r.objekt_id
         AND t1.id > r.id
         AND (t1.person_id <> r.person_id OR r.person_id  IS NULL)
         AND t1.person_id IS NOT NULL
         ORDER BY t1.id  
         LIMIT 1) next_person
    FROM tbl r
order by 1;

    sqlfiddle

  • answered 2022-05-04 11:42 MatBailie

    It is possible with window functions, but while I'm on my phone I'm struggling to work out a concise answer as PostGreSQL doesn't have IGNORE NULLS.

    For now, here's a clunky answer...

    with
  tbl as
(
  -- From your question, but fixed by moving the `ORDER BY` into the window function
  SELECT
    rent.*,
    row_number() over (PARTITION BY objekt_id ORDER BY start_date) as row_id
  FROM
    rent
),
  lag_lead AS
(
  -- do a naive lag and lead, not yet trying to account for nulls
  -- if the result is the same as the current row, replace with NULL
  -- (thus only identifying lag/lead values where the's a change)
  SELECT
    *,
    NULLIF(LAG( person_id) over (PARTITION BY objekt_id ORDER BY start_date), person_id)   AS last_person,
    NULLIF(LEAD(person_id) over (PARTITION BY objekt_id ORDER BY start_date), person_id)   AS next_person
  FROM
    tbl
),
  identify_partitions AS
(
  -- create groups of rows where the results should be the same
  SELECT
    *,
    COUNT(new_last_person) OVER (PARTITION BY objekt_id ORDER BY start_date  ASC)  AS last_person_partition,
    COUNT(new_next_person) OVER (PARTITION BY objekt_id ORDER BY start_date DESC)  AS next_person_partition
  FROM
    lag_lead
)
SELECT
  *,
  MAX(new_last_person) OVER (PARTITION BY objekt_id, last_person_partition)   AS real_last_person,
  MAX(new_next_person) OVER (PARTITION BY objekt_id, next_person_partition)   AS real_next_person
FROM
  identify_partitions
ORDER BY
  1;

    https://dbfiddle.uk/?rdbms=postgres_10&fiddle=b613f88a730cfddcef4efb612b6e236c

    In that example I've amended your data slightly, to demonstrate the behaviour if the person_id transitions from X to NULL and back to X.

    • If you require different behaviour, please comment

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum

See also questions close to this topic

  • Why does this Sql query shows the possible changes but does not implement it?

    So, I want to change the prefix of my tables and the following command shows the possible changes that will take place which seems alright but does not seem to implement it.

    SELECT Concat('RENAME TABLE ', TABLE_NAME, ' TO fan_', SUBSTRING_INDEX(TABLE_NAME, 'pc_',-1), ';') FROM information_schema.tables WHERE table_name like 'pc_%' and table_schema='testdbhere'

    Moreover, this isn't a writing privilege issue as changing the tables name individually works perfectly from the same user.

  • how to get weeklytotal and yesterday record in mysql in one table

    Hi Everyone i am trying to implement query to get weekly and yesterday data in same table, dummy output i have shared below, if yesterday not exist as per employee_id it should we showing 0 also as per my table week start from monday and end at sunday.please help me out how to query this get weekly_total and yesterday record and one table.

    Table name-dailydata-

    Sample data

    employee_id date total
    20 2022-04-25 10
    20 2022-04-26 20
    20 2022-04-27 20
    20 2022-04-28 10
    20 2022-04-29 20
    20 2022-04-30 30
    20 2022-04-31 40
    20 2022-05-01 50
    40 2022-04-26 20

    expected output

    employee_id weekly_total yesterday_record
    20 200 40
    40 20 0

    mysql query to get weekly data

    select employee_id,sum(total) as week_total from dailydata where date between '2022-04-25' and '2022-05-01'
  • Procedure to display the Employees of a specific Department

    Create a stored procedure, to display the lastnames as "Name" from the employee table. The requirement is as below: The name of the Procedure : EmployeesDept

    Name of the argument as Input : DeptNo

    Write the code to create the procedure

    Schema

    I wrote the Query like this but i didnt get expected output

    CREATE  PROCEDURE Employeesdept(@Deptno varchar)
AS
Begin
SELECT lastname as name
from employee
where workdept= 'D21'
end

    Expected output:

    Name --------------- Pulaski Jefferson Marino Smith Johnson Perez Monteverde Name ---------------

  • ERROR: invalid byte sequence for encoding WITH psql

    I've seen numerous issues in other posts with the copy command and:

    ERROR:  invalid byte sequence for encoding "UTF8": 0xfc

    And the consensus in these posts appears to be to specify the encoding in the command you're doing the copy with. I have done so:

    psql -h localhost -p 5432 -d BOBDB -U BOB -c "\COPY \"BOBTB01\" FROM 'C:\Temp\file.csv' with csv HEADER ENCODING 'WIN1252'";
Password for user BOB:
**ERROR:  character with byte sequence 0x81 in encoding "WIN1252" has no equivalent in encoding "UTF8"
CONTEXT:  COPY BOBTB01, line 76589**

    So, that confused me and I changed to UTF8 to WIN1252 and having done so I get a slightly different error, the failure is on a different line and the text is slightly different.

    psql -h localhost -p 5432 -d BOBDB -U BOB -c "\COPY \"BOBTB01\" FROM 'C:\Temp\file.csv' with csv HEADER ENCODING 'UTF8'";
Password for user BOB:
**ERROR:  invalid byte sequence for encoding "UTF8": 0xfc
CONTEXT:  COPY BOBTB01, line 163**

    This is the encoding shown in the database:

    show client_encoding;
 client_encoding
-----------------
 UTF8
(1 row)

    The file is from a reliable source and I happen to have "R" installed which also does .csv import. The file was pulled into "R" without issue, that's making me think it's not the file but something else. Is there another switch or syntax that can bypass these issues perhaps?

    I'm not sure what is wrong.

    Can you help?

    Thanks.

  • Whats missing on my Ruby 'Inverse Of' relationship

    I know this topic has been addressed, but I have been at this for 2 days and I'm just stuck. I know inverse of does not create a new query, so should I use another method?

    Question: How to set up an 'inverse of' with a has_one, belongs_to situation & same class..

    Explanation: A user 'has_one :spouse' and 'belongs_to :spouse_from'. They are inverse of each other. When a User signs up, they can invite their significant other. For Example

    1. user_a invites & creates user_b
    2. user_b.spouse_id is set to user_a.id
    3. In a separate method I want to be able to update like.. user_a.spouse_id = user_a.spouse.id

    The only association that works at this point is user_b.spouse.

     Class User
  has_one :spouse, class_name: 'User', foreign_key: :spouse_id, dependent: :nullify, inverse_of: :spouse_from


  belongs_to :spouse_from, class_name: 'User', foreign_key: :spouse_id, inverse_of: :spouse, optional: true
  • Normalizing data in postgresql

    Flag This application will read an iTunes library in comma-separated-values (CSV) and produce properly normalized tables as specified below. Once you have placed the proper data in the tables, press the button below to check your answer.

    We will do some things differently in this assignment. We will not use a separate "raw" table, we will just use ALTER TABLE statements to remove columns after we don't need them (i.e. we converted them into foreign keys).

    We will use the same CSV track data as in prior exercises - this time we will build a many-to-many relationship using a junction/through/join table between tracks and artists.

    To grade this assignment, the program will run a query like this on your database and look for the data it expects to see:

    
SELECT track.title, album.title, artist.name
FROM track
JOIN album ON track.album_id = album.id
JOIN tracktoartist ON track.id = tracktoartist.track_id
JOIN artist ON tracktoartist.artist_id = artist.id
ORDER BY track.title
LIMIT 3;

    Expected out put is this

    The expected result of this query on your database is: title album artist A Boy Named Sue (live) The Legend Of Johnny Cash Jo

    
DROP TABLE album CASCADE;
CREATE TABLE album (
    id SERIAL,
    title VARCHAR(128) UNIQUE,
    PRIMARY KEY(id)
);

DROP TABLE track CASCADE;
CREATE TABLE track (
    id SERIAL,
    title TEXT, 
    artist TEXT, 
    album TEXT, 
    album_id INTEGER REFERENCES album(id) ON DELETE CASCADE,
    count INTEGER, 
    rating INTEGER, 
    len INTEGER,
    PRIMARY KEY(id)
);

DROP TABLE artist CASCADE;
CREATE TABLE artist (
    id SERIAL,
    name VARCHAR(128) UNIQUE,
    PRIMARY KEY(id)
);

DROP TABLE tracktoartist CASCADE;
CREATE TABLE tracktoartist (
    id SERIAL,
    track VARCHAR(128),
    track_id INTEGER REFERENCES track(id) ON DELETE CASCADE,
    artist VARCHAR(128),
    artist_id INTEGER REFERENCES artist(id) ON DELETE CASCADE,
    PRIMARY KEY(id)
);

\copy track(title,artist,album,count,rating,len) FROM 'library.csv' WITH DELIMITER ',' CSV;

INSERT INTO album (title) SELECT DISTINCT album FROM track;
UPDATE track SET album_id = (SELECT album.id FROM album WHERE album.title = track.album);

INSERT INTO tracktoartist (track, artist) SELECT DISTINCT ...

INSERT INTO artist (name) ...

UPDATE tracktoartist SET track_id = ...
UPDATE tracktoartist SET artist_id = ...

-- We are now done with these text fields
ALTER TABLE track DROP COLUMN album;
ALTER TABLE track ...
ALTER TABLE tracktoartist DROP COLUMN track;
ALTER TABLE tracktoartist ...

SELECT track.title, album.title, artist.name
FROM track
JOIN album ON track.album_id = album.id
JOIN tracktoartist ON track.id = tracktoartist.track_id
JOIN artist ON tracktoartist.artist_id = artist.id
LIMIT 3;

    What am i doing wrong with the code?

  • The bootstrap navbar collapse animation is lagging and behaving strangely 

    var nav = document.querySelector('nav');

      window.addEventListener('scroll', function () {
        if (window.pageYOffset > 40) {
          nav.classList.add('bg-white');
        } else {
          nav.classList.remove('bg-white');
        }
      });
      

      
      $(document).ready(function(){
        $('#nav-icons').click(function(){
          $(this).toggleClass('open');
        });
      });


      $(document).ready(function() {
        $('.client').slick({
            autoplay: false,
            autoplaySpeed: 1000,
            speed: 600,
            draggable: true,
            infinite: true,
            slidesToShow: 4,
            slidesToScroll: 4,
            arrows: true,
            responsive: [
                {
                  breakpoint: 991,
                  settings: {
                    slidesToShow: 3,
                    slidesToScroll: 3,
                  }
                },
                {
                  breakpoint: 767,
                  settings: {
                    slidesToShow: 3,
                    slidesToScroll: 3,
                  }
                },
                {
                    breakpoint: 575,
                    settings: {
                      slidesToShow: 2,
                      slidesToScroll: 2,
                    }
                }
            ]
        });
    }); 


    $(window).on("load", function() {
      $('.spinner').fadeOut(300);
      $("body").addClass("pageloaded");
  });
    :root {
  --primary-color:#0071ce;
  --secondary-color:#ffe800;
}
/* Navbar */
button {
  color: transparent;
}
.navbar {
  visibility: hidden;
}
.pageloaded .navbar {
  visibility: visible;
}
.navbar-custom .nav-link{
  font-family: 'ProximaNova-Semibold', Courier, monospace;
  text-transform: uppercase;
  color: var(--primary-color);
  display: block;
  font-size: 1.0rem;
}
.navbar-custom .nav-link a{
  padding: 50rem;
}
.navbar-custom{
  padding: 25px;
  padding-left: 25px;
  background-color: rgb(255, 255, 255);
}
.navbar-custom a{
  padding: 5px 10px;
  display: block;
}
.navbar-custom a:hover{
  text-decoration: none;
  color: var(--primary-color);
}
.navbar-custom a.active{
  color: var(--secondary-color);
}
#nav-icons {
  width: 35px;
  height: 45px;
  position: relative;
  margin: 6px auto;
  -webkit-transform: rotate(0deg);
  -moz-transform: rotate(0deg);
  -o-transform: rotate(0deg);
  transform: rotate(0deg);
  -webkit-transition: 0.4s ease-in-out;
  -moz-transition: 0.4s ease-in-out;
  -o-transition: 0.4s ease-in-out;
  transition: 0.4s ease-in-out;
  cursor: pointer;
}
#nav-icons span {
  display: block;
  position: absolute;
  height: 5px;
  width: 100%;
  background: var(--primary-color);
  opacity: 1;
  left: 0;
  -webkit-transform: rotate(0deg);
  -moz-transform: rotate(0deg);
  -o-transform: rotate(0deg);
  transform: rotate(0deg);
  -webkit-transition: .25s ease-in-out;
  -moz-transition: .25s ease-in-out;
  -o-transition: .25s ease-in-out;
  transition: .25s ease-in-out;
}
#nav-icons span:nth-child(1) {
  top: 0px;
  -webkit-transform-origin: center;
  -moz-transform-origin: center;
  -o-transform-origin: center;
  transform-origin: center;
}
#nav-icons span:nth-child(2) {
  top: 11px;
  -webkit-transform-origin: center;
  -moz-transform-origin: center;
  -o-transform-origin: center;
  transform-origin: center;
}
#nav-icons span:nth-child(3) {
  top: 21px;
  -webkit-transform-origin: center;
  -moz-transform-origin: center;
  -o-transform-origin: center;
  transform-origin: center;
}
#nav-icons.open span:nth-child(1) {
  -webkit-transform: rotate(-45deg);
  -moz-transform: rotate(-45deg);
  -o-transform: rotate(-45deg);
  transform: rotate(-45deg);
  top: 10px;
  left: 2px;
}
#nav-icons.open span:nth-child(2) {
  width: 0%;
  opacity: 0;
}
#nav-icons.open span:nth-child(3) {
  -webkit-transform: rotate(45deg);
  -moz-transform: rotate(45deg);
  -o-transform: rotate(45deg);
  transform: rotate(45deg);
  top: 10px;
  left: 4px;
}


/* Media query */
@media only screen and (max-width: 767px) {
  .navbar-custom{
      padding: 0px;
      padding-left: 25px;
      padding-top: 5px;
      padding-bottom: 5px;
 }
  .navbar-collapse{
      display: block;
      position: absolute;
      width: 150vw;
      height: 150vh;
      left: 0;
      top: 0;
      background: var(--secondary-color);
      z-index: 2;
 }
  #nav-icons{
      z-index: 3;
 }
  .navbar-collapse .navbar-nav{
      padding-top: 120px;
      padding-left: 30px;
 }

}
    <!DOCTYPE html>
<html lang="en">
   <head>
      <meta charset="UTF-8">
      <meta http-equiv="X-UA-Compatible" content="IE=edge">
      <meta name="viewport" content="width=device-width, initial-scale=1.0">
      <link rel="shortcut icon" href="./images/social-and-icons/favicon.ico" type="image/x-icon">
      <title>Welcome To my website</title>
      <!-- css -->
      <link rel="stylesheet" href="./css/style.css">
      <!-- Bootstrap 5 -->
      <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-EVSTQN3/azprG1Anm3QDgpJLIm9Nao0Yz1ztcQTwFspd3yD65VohhpuuCOmLASjC" crossorigin="anonymous">
   </head>
   <body>
      <header>
         <nav class="navbar navbar-expand-md navbar-custom fixed-top">
            <div class="container-fluid">
               <a  class="navbar-brand" href="index.html"><img src="./images/logo.png" width="78" height="100" alt="logo"></a>
               <button class="navbar-toggler" type="button" data-bs-toggle="collapse" data-bs-target="#navbarSupportedContent" aria-controls="navbarSupportedContent" aria-expanded="false" aria-label="Toggle navigation">
                  <div id="nav-icons">
                     <span></span><span></span><span></span>
                  </div>
               </button>
               <div class="collapse navbar-collapse" id="navbarSupportedContent">
                  <ul class="navbar-nav ms-auto mb-2 mb-lg-0">
                     <li   class="nav-item">
                        <a class="nav-link" aria-current="page" href="#">About Us</a>
                     </li>
                     <li  class="nav-item">
                        <a class="nav-link" href="#">About Us</a>
                     </li>
                     <li class="nav-item">
                        <a class="nav-link" href="#">About Us</a>
                     </li>
                     <li  class="nav-item">
                        <a class="nav-link" href="#">About Us</a>
                     </li>
                  </ul>
               </div>
            </div>
         </nav>
      </header>
      <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
      <script src="https://cdnjs.cloudflare.com/ajax/libs/slick-carousel/1.8.1/slick.min.js"></script>
      <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/js/bootstrap.bundle.min.js" integrity="sha384-MrcW6ZMFYlzcLA8Nl+NtUVF0sA7MsXsP1UyJoMp4YLEuNSfAP+JcXn/tWtIaxVXM" crossorigin="anonymous"></script>
      <script src="./js/script.js"></script>

    I need assistance with bootstrap navbar collapse. When I click the burger menu, there is a short delay before it fully expands. enter image description here enter image description here Also, if you click very quickly, such as multiple times on the burger icon, the x icon animation is delayed, and the x becomes the closed icon rather than the burger icons, enter image description here which is annoying because I like to have a bit of delay with my nav collapse. Please see my link below for a live version:

  • How show mysql query without NULL?

    How show mysql query without NULL? I want to show my quary where "LAG(title) OVER(PARTITION BY emp_no)" IS NOT NULL, I need only titles.emp_no = "LAG(title) OVER(PARTITION BY emp_no)"

    My query:

    SELECT titles.emp_no, LAG(title) OVER(PARTITION BY emp_no) FROM titles;

    enter image description here

  • Lag when clicking on the autocomplete suggestion of a Windows Forms App

    When I click on an autocomplete suggestion, there is always a tiny lag as you can see on the video.

    Demo Video

    This is a Windows Forms App and the code for the autocomplete is:

    var source = new AutoCompleteStringCollection();
        source.AddRange(new string[]
                    {           
                        "Eggs",
                        "Tomatoes",
                        "Milk",
                        "Apples",
                        "Bananas",
                        "Cheese",   
                    });

        product.AutoCompleteCustomSource = source;
        product.AutoCompleteMode = AutoCompleteMode.SuggestAppend;
        product.AutoCompleteSource = AutoCompleteSource.CustomSource;

    As you can see the data is hardcoded.

    I'm using:

    • Microsoft .NET Framework
    • Version 4.8.04161
    • Visual C++ 2022 00482-90000-00000-AA777
    • Microsoft Visual C++ 2022
    • ASP.NET and Web Tools 2019 17.1.358.51495
  • Lag and lead a variable in a dataframe by 1 month and 6 business days for panel data

    I have a large panel data set and I would like to lag and lead a variable by 1 month and 6 business days. I know, for instance, from dplyr there is the lag or lead function. However, I also need to group by data based on the "Names" in the panel data.

    My data look like this:

    structure(list(Date = c("01.08.2018", "02.08.2018", "03.08.2018", 
"04.08.2018", "05.08.2018", "06.04.2019", "07.04.2019", "08.04.2019", 
"01.08.2018", "02.08.2018", "03.08.2018", "04.08.2018", "06.04.2019", 
"07.04.2019", "08.04.2019", "01.08.2018", "02.08.2018", "03.08.2018", 
"04.08.2018", "05.08.2018", "07.04.2019", "08.04.2019"), Name = c("A", 
"A", "A", "A", "A", "A", "A", "A", "A", "B", "B", "B", "B", "B", 
"B", "C", "C", "C", "C", "C", "C", "C"), Rating = c(1L, 1L, 1L, 
3L, 3L, 4L, 4L, 4L, 3L, 3L, 2L, 2L, 2L, 1L, 1L, 1L, 3L, 3L, 3L, 
5L, 5L, 5L), Size = c(1234L, 24123L, 23L, 1L, 23L, 3L, 23L, 4L, 
323L, 3424L, 523L, 234L, 35L, 354L, 45L, 23L, 46L, 456L, 546L, 
24L, 134L, 1L)), class = "data.frame", row.names = c(NA, -22L
))

    It is just a simplified version. My real data lasts from 01.08.2018 to 31.12.2021. How can I only lag and lead the variable called "Rating" by 1 month and 6 business days?

    My difficulty is that I have 1 month and 6 business days and not just one variable in the dataframe. All the other variables should not be adjusted.

    So far I tried this:

    Data_2  <- Data  %>%
  group_by(Name)  %>%
  lag('Rating')

    Data_3  <- Data  %>%
  group_by(Name)  %>%
  lead('Rating')

    But this is not what I am aiming for.

    EDIT:

    My output should look like this in the case of lead: (I just used the first 5 rows to illustrate)

    structure(list(Date = c("10.09.2018", "11.09.2018", "12.09.2018", 
"13.09.2018", "14.09.2018"), Name = c("A", "A", "A", "A", "A"
), Rating = c(1L, 1L, 1L, 3L, 3L), Size = c("Size from 10.09.2018 would be here", 
"Size from 11.09.2018 would be here", "Size from 12.09.2018 would be here", 
"Size from 13.09.2018 would be here", "Size from 14.09.2018 would be here"
)), class = "data.frame", row.names = c(NA, -5L))

    So for row 1 I added 1 month and 6 business days which gives me 10.09.2018 and so on. The "Rating" will then be the one from 01.08.2018 but the "Size" will be the figure that was actually also reported on 10.09.2018. Then, I would like to do the same but go backwards 1 month and 6 business days.

  • Is there a way to get a leadgen_id of the people after the lead form is submitted?

    We are working on getting Facebook lead details into our system through webhook.

    Once the FB lead form is submitted by the people, we want them to be redirected to another screening form which is present in our system.

    Now we want to identify the lead to map the details given in the instant form and screening form. With the subscription, once the lead form is submitted, we got the below response,

    { "object": "page", "entry": [ { "id": "12345678", "time": 1649921293, "changes": [ { "value": { "form_id": "12345678", "leadgen_id": "12345678", "created_time": 12345678, "page_id": "12345678" }, "field": "leadgen" } ] } ] }

    If we could get the leadgen_id in the page that is given as Call to action Website link, then we could relate the lead with the screening form details.

    custom form link once instant form is submitted