# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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?

Output Table Should be

1 Solution

Accepted Solutions
MVP

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

11 Replies
MVP

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

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

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

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

Contributor II

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

It's working now, Thanks Sunny.

MVP

Great

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:

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

];

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?

Contributor II

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