11 Replies Latest reply: Sep 6, 2017 4:03 AM by RAM MUTHIAH M

# 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?

Output Table Should be

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

Try this

Table1:

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:

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)

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

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

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;

• ###### 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

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

It's working now, Thanks Sunny.

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

Great

• ###### 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:

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:

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

];

• ###### 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?

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

Table1:

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:

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 :

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

I went through you logic above and also tried to look at the new sample, but I am not sure what exactly are you looking to get and how you are looking to get it. May be someone else can offer help here

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

Thanks for responding patiently, Sunny.
If the SD value present in Table1, then it should not show PL value from Table 2. For instance,
Use        Row 1-> 1, Ax, 30-08-2017, 2,1(SD) ------ Table 1
Use        Row 1->  1, Ax, 30-07-2017, 2,1(PL) ------ Table 2
Use        Row 2->  1, Ax, 30-07-2017, 2,2(PL) ------ Table 2

Output : Should be one row - 1, Ax, 30-07-2017, 2,1(SD) ----- Result (SD Value =1, no need of looking for PL value 1 or 2, if SD value is not present in Table 1, then show PL value from Table 2)

For me it is showing as 1, Ax, 30-07-2017, 2,1 ----T1
1, Ax, 30-07-2017, 2,1 ----T2
1, Ax, 30-07-2017, 2,2 ----T2