Load dates table - use of previous() function

[jaymerry]

Hello all,

I need to load a file with the following fields and contents :

Date1	Date2
01/01/2012
02/01/2012	30/12/2012
03/01/2012
04/01/2012
05/01/2012	31/12/2012
06/01/2012

In my load script, I need to transform it to have a table which looks like that :

Date1	Date2
01/01/2012
02/01/2012	30/12/2012
03/01/2012	30/12/2012
04/01/2012	30/12/2012
05/01/2012	31/12/2012
06/01/2012	31/12/2012

To sumarize, I need to have for each Date1 a Date2 value which will be same that the last Date2.

This example is simplified, in my real data, I have one Date2 by month on one Date1.

I have all the dates in Date1 for several years.

I have tried to use the "previous()" function, but the load is very long :

     File:
     Load
          Date1,
          Date2,
          rowno() as init
     from file;
    NB:
    Load
    maxstring(init) as Row
    RESIDENT File;
    let vCount = Peek('Row',0,NB);
     😧
    LOAD 
        Date1,
        Date2
    RESIDENT File;    
           for i=1 to vCount    
            😧
            JOIN LOAD
                 Date1,
                 if((Date2)='',previous(Date2),Date2) as Date2,
            RESIDENT D
            WHERE (Date2)='' 
            order by Date1 ASC;
        next i

Have you an idea about this problem ?

Thank you for your help,

Best regards,

Jaymerry

[Anonymous · Accepted solution]

Use the peek() function to simply pull the above value in case the value is null:

Load

          Date1,

          if(len(Date2)=0,peek('Date2'),Date2) as Date2;

Load * Inline [

Date1,Date2

01/01/2012,

02/01/2012,30/12/2012

03/01/2012,

04/01/2012,

05/01/2012,31/12/2012

06/01/2012,

];

[jaymerry]

Hello Johannes,

Thank you for your answer.

With your example, I will have the following result :

Date1	Date2
01/01/2012
02/01/2012	30/12/2012
03/01/2012	30/12/2012
04/01/2012
05/01/2012	31/12/2012
06/01/2012	31/12/2012

All the Date2 following will not be filled. How can I do to fill all the Date2 with this example :

Date1	Date2
01/01/2012
02/01/2012	30/12/2012
03/01/2012
04/01/2012
05/01/2012	31/12/2012
06/01/2012	31/12/2012
07/01/2012
08/01/2012
09/01/2012
10/01/2012
11/01/2012
12/01/2012
13/01/2012
14/01/2012
15/01/2012
16/01/2012
17/01/2012
18/01/2012
19/01/2012
20/01/2012
21/01/2012
22/01/2012
23/01/2012
24/01/2012
25/01/2012
26/01/2012
27/01/2012
28/01/2012
29/01/2012
30/01/2012

Thanks for your help,

Jaymerry

[#4]

Hello

Can you upload your example?

The function Peek run ok that say Johannes. I try your example and run ok.

Thanks.

[jaymerry]

You're right,

Thanks to you and Johannes for your help