Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

rammuthiah
Contributor II

How to get Previous Date from Table 2 for Max Date in Table 1

Here I have one logic that needs to be done as given in the below excel output. Can anyone help me out in this logic?

             Input.jpg

Output Table Should be

Output.png

1 Solution

Accepted Solutions
MVP
MVP

Re: How to get Previous Date from Table 2 for Max Date in Table 1

Try this

Table1:

LOAD * INLINE [

    PN, CN, T1 Date, Cost

    1, Ax, 30-08-2017, 2

    1, Ax, 30-08-2017, 2

    1, Bx, 30-08-2017, 2.5

    1, Cx, 30-08-2017, 2.5

];

Table2:

LOAD * INLINE [

    PN, CN, T2 Date, Value

    1, Ax, 30-07-2017, 2

    1, Ax, 26-08-2017, 2.25

    1, Ax, 27-08-2017, 2.15

    1, Ax, 30-08-2017, 2

    1, Bx, 27-08-2017, 2.2

    1, Bx, 28-08-2017, 2.35

    1, Bx, 30-08-2017, 2.5

    1, Cx, 25-08-2017, 2.5

    1, Cx, 30-08-2017, 2.5

];

Left Join (Table1)

LOAD *,

Date(Start_T2_Date + IterNo() - 1) as [T1 Date]

While Start_T2_Date + IterNo() - 1 <= Start_T2_Date;

LOAD *,

Date(If(PN = Previous(PN) and CN = Previous(CN), Previous([T2 Date]), MakeDate(9999, 12, 31))) as Start_T2_Date,

Date([T2 Date] + 1) as End_T2_Date

Resident Table2

Order By PN, CN, [T2 Date] desc;

DROP Table Table2;

11 Replies
MVP
MVP

Re: How to get Previous Date from Table 2 for Max Date in Table 1

Try this

Table1:

LOAD * INLINE [

    PN, CN, T1 Date, Cost

    1, Ax, 30-08-2017, 2

    1, Ax, 30-08-2017, 2

    1, Bx, 30-08-2017, 2.5

    1, Cx, 30-08-2017, 2.5

];

Table2:

LOAD * INLINE [

    PN, CN, T2 Date, Value

    1, Ax, 30-07-2017, 2

    1, Ax, 26-08-2017, 2.25

    1, Ax, 27-08-2017, 2.15

    1, Ax, 30-08-2017, 2

    1, Bx, 27-08-2017, 2.2

    1, Bx, 28-08-2017, 2.35

    1, Bx, 30-08-2017, 2.5

    1, Cx, 25-08-2017, 2.5

    1, Cx, 30-08-2017, 2.5

];

Left Join (Table1)

LOAD *,

Date(Start_T2_Date + IterNo() - 1) as [T1 Date]

While Start_T2_Date + IterNo() - 1 <= Start_T2_Date;

LOAD *,

Date(If(PN = Previous(PN) and CN = Previous(CN), Previous([T2 Date]), MakeDate(9999, 12, 31))) as Start_T2_Date,

Date([T2 Date] + 1) as End_T2_Date

Resident Table2

Order By PN, CN, [T2 Date] desc;

DROP Table Table2;

rammuthiah
Contributor II

Re: How to get Previous Date from Table 2 for Max Date in Table 1

I tried your way. For me in Qlik Sense, Here find below screenshot

Capture.JPG

antoniotiman
Honored Contributor III

Re: How to get Previous Date from Table 2 for Max Date in Table 1

Hi,

do You have in Script

SET DateFormat='DD-MM-YYYY';

MVP
MVP

Re: How to get Previous Date from Table 2 for Max Date in Table 1

antoniotiman‌ is right, it seems that your date field may not be read as date... look here

Why don’t my dates work?

Get the Dates Right

QlikView Date fields

rammuthiah
Contributor II

Re: How to get Previous Date from Table 2 for Max Date in Table 1

It's working now, Thanks Sunny.

MVP
MVP

Re: How to get Previous Date from Table 2 for Max Date in Table 1

Great

rammuthiah
Contributor II

Re: How to get Previous Date from Table 2 for Max Date in Table 1

Here I am including SD in Table 1 and PL in Table 2, if SD for PN = 1, then show PN only for PL=1 in Table 2, if SD for PN = 2, then show PN only for PL=2 in Table 2. I tried loading seperately for SD=1 and SD =2 and Concatenated both using where not exist condition. But it doesn't work....

Table1:

LOAD * INLINE [

    PN, CN, T1 Date, Cost, SD

    1, Ax, 30-08-2017, 2,1

    1, Ax, 30-08-2017, 2,2

    1, Bx, 30-08-2017, 2.5,1

    1, Cx, 30-08-2017, 2.5,1

];

Table2:

LOAD * INLINE [

    PN, CN, T2 Date, Value, PL

    1, Ax, 30-07-2017, 2,1

    1, Ax, 26-08-2017, 2.25,2

    1, Ax, 27-08-2017, 2.15,1

    1, Ax, 30-08-2017, 2,1

    1, Bx, 27-08-2017, 2.2,2

    1, Bx, 28-08-2017, 2.35,1

    1, Bx, 30-08-2017, 2.5,1

    1, Cx, 25-08-2017, 2.5,1

    1, Cx, 30-08-2017, 2.5,1

];

MVP
MVP

Re: How to get Previous Date from Table 2 for Max Date in Table 1

Not sure I understand.. what is the expected output here?

rammuthiah
Contributor II

Re: How to get Previous Date from Table 2 for Max Date in Table 1

Table1:

LOAD * INLINE [

    PN, CN, T1 Date, Cost, SD

    1, Ax, 30-08-2017, 2,1

    1, Ax, 30-08-2017, 2,2

    1, Bx, 30-08-2017, 2.5,2

    1, Cx, 30-08-2017, 2.5,2

];

Table2:

LOAD * INLINE [

    PN, CN, T2 Date, Value, PL

    1, Ax, 30-07-2017, 2,1

    1, Ax, 26-08-2017, 2.25,2

    1, Ax, 27-08-2017, 2.15,1

    1, Ax, 30-08-2017, 2,1

    1, Bx, 27-08-2017, 2.2,2

    1, Bx, 28-08-2017, 2.35,1

    1, Bx, 30-08-2017, 2.5,2

    1, Cx, 25-08-2017, 2.5,1

    1, Cx, 30-08-2017, 2.5,2

];

Output :

Output.png