Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
rammuthiah
Creator III
Creator III

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
sunny_talwar

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;

View solution in original post

11 Replies
sunny_talwar

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
Creator III
Creator III
Author

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

Capture.JPG

antoniotiman
Master III
Master III

Hi,

do You have in Script

SET DateFormat='DD-MM-YYYY';

sunny_talwar

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
Creator III
Creator III
Author

It's working now, Thanks Sunny.

sunny_talwar

Great

rammuthiah
Creator III
Creator III
Author

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

];

sunny_talwar

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

rammuthiah
Creator III
Creator III
Author

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