Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Doubt in Interval Match

I have the below dataset :-

Capture.PNG

And I want this Output :-

answer.PNG

can anyone help me to do this....

1 Solution

Accepted Solutions
aarkay29
Specialist
Specialist

Try this:


Table:

Load F1,Date#(F2,'DD/MM/YYYY') as F2;

LOAD * INLINE [

    F1, F2,F3

    1,12/12/2016,90

    1,01/01/2017,92

    2,01/02/2017,56

    2,22/02/2017,59

  

];

Load

     F1,

     F2 as StartDate,

     If(F1=peek(F1),Date(peek(StartDate)-1,'DD/MM/YYYY'),Date(today())) as [End Date]

Resident

       Table

Order By

      F1,F2 Desc;

Drop Table

     Table;

View solution in original post

12 Replies
aarkay29
Specialist
Specialist

Try this:


Table:

Load F1,Date#(F2,'DD/MM/YYYY') as F2;

LOAD * INLINE [

    F1, F2,F3

    1,12/12/2016,90

    1,01/01/2017,92

    2,01/02/2017,56

    2,22/02/2017,59

  

];

Load

     F1,

     F2 as StartDate,

     If(F1=peek(F1),Date(peek(StartDate)-1,'DD/MM/YYYY'),Date(today())) as [End Date]

Resident

       Table

Order By

      F1,F2 Desc;

Drop Table

     Table;

Anonymous
Not applicable
Author

Thanks Aar Kay...

Load F1,Date#(F2,'DD/MM/YYYY') as F2; // why we are using this preceeding Load


and

If(F1=peek(F1),Date(peek(StartDate)-1,'DD/MM/YYYY'),Date(today())) as [End Date]

Can you explain me the above line



aarkay29
Specialist
Specialist

preceeding load is just to tell qlikview that F2 is date field in 'DD/MM/YYYY' format for the sample data.

If(F1=peek(F1) -this is if field value of F1 is equal to previous row value of F1,

then Date(peek(StartDate)-1,'DD/MM/YYYY') i.e take value of startdate "minus" 1day from  the previousrow

if field value of F1 is "not" equal to previous F1 then return today()

Anonymous
Not applicable
Author

if I want  F3 Column also in the resultant table. for this what I need to do..

because simply Loading gives me an error that the F3 column is not found..

aarkay29
Specialist
Specialist

Try this:


Table:

Load *,Date#(F2,'DD/MM/YYYY') as F2;

LOAD * INLINE [

    F1, F2,F3

    1,12/12/2016,90

    1,01/01/2017,92

    2,01/02/2017,56

    2,22/02/2017,59

 

];

Load

     F1,

     F2 as StartDate,

     If(F1=peek(F1),Date(peek(StartDate)-1,'DD/MM/YYYY'),Date(today())) as [End Date],

     F3

Resident

       Table

Order By

      F1,F2 Desc;

Drop Table

     Table;

Anonymous
Not applicable
Author

But it gives me an error that F3 is not found

aarkay29
Specialist
Specialist

Did you change F1 to *  in the preceding load


Table:

Load *,Date#(F2,'DD/MM/YYYY') as F2;

LOAD * INLINE [

    F1, F2,F3

    1,12/12/2016,90

    1,01/01/2017,92

    2,01/02/2017,56

    2,22/02/2017,59

];

Load

    F1,

    F2 as StartDate,

    If(F1=peek(F1),Date(peek(StartDate)-1,'DD/MM/YYYY'),Date(today())) as [End Date],

    F3

Resident

      Table

Order By

      F1,F2 Desc;

Drop Table

    Table;

Anonymous
Not applicable
Author

Below is the code which i tried to execute but gives error :-

interval match.PNG

Gives the following error :-

Interval.PNG

aarkay29
Specialist
Specialist

Sorry About that because of load * -F2 field is being loaded twice that is why it is giving you error

instead of Load *,Date#(F2,'DD/MM/YYYY') as F2;

Use this  Load F1,F3,Date#(F2,'DD/MM/YYYY') as F2;