Doubt in Interval Match

[Anonymous]

I have the below dataset :-

And I want this Output :-

can anyone help me to do this....

[aarkay29 · Accepted solution]

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]

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]

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]

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]

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]

But it gives me an error that F3 is not found

[aarkay29]

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]

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

Gives the following error :-

[aarkay29]

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;