topic Re: Left Join to a max date in QlikView

Left Join to a max date

tinkerz1 — Mon, 30 Nov 2015 11:33:40 GMT

Hi,

I am trying to write max date in the load script and then left join.

This should give me only the ID's with the lastes load date bu QV is bringing back everything.

IE.

Month-Year Item ID

12/01/2015 1

12/01/2015 2

12/02/2015 3

12/02/2015 4

I should just bring back 3 and 4.

load
max([Month-Year Item]) as [Month-Year Item2]

RESIDENT [Download];
Left Join(CT2)
load
[ID]
RESIDENT [Download]
where len([ID])>0
;

Re: Left Join to a max date

PrashantSangle — Mon, 30 Nov 2015 11:36:32 GMT

Hi,

if you want latest ID then use FirstSortedValue()

Regards

Re: Left Join to a max date

sunny_talwar — Mon, 30 Nov 2015 11:37:41 GMT

Try right join:

Table:

LOAD * Inline [

Month-Year, Item ID

12/01/2015, 1

12/01/2015, 2

12/02/2015, 3

12/02/2015, 4

];

Right Join (Table)

LOAD Max([Month-Year]) as [Month-Year]

Resident Table;

Re: Left Join to a max date

Anonymous — Mon, 30 Nov 2015 11:44:55 GMT

for joining you need atleast a common field.

In your logic

load
max([Month-Year Item]) as [Month-Year Item2]

RESIDENT [Download];
Left Join(CT2)
load
[ID]
RESIDENT [Download]
where len([ID])>0

Their is no common field and you are joining both the table without any key between them. Thats why you are getting all the ID's becoz

this

load
[ID]
RESIDENT [Download]
where len([ID])>0

will return all the ids like 1,2,3,4 in your case

See this, will be helpful for you:

Understanding Join, Keep and Concatenate

Re: Left Join to a max date

jyothish8807 — Mon, 30 Nov 2015 11:47:12 GMT

Hi Neil,

Try using inner join,

Table:

LOAD * Inline [

Month-Year, Item ID

12/01/2015, 1

12/01/2015, 2

12/02/2015, 3

12/02/2015, 4

];

InnerJoin (Table)

LOAD Max([Month-Year]) as [Month-Year]

Resident Table;

Regards

Re: Left Join to a max date

tinkerz1 — Mon, 30 Nov 2015 14:02:16 GMT

Hi,

This code below works for me, but can I group on First Value and then right join on that?

[CT2]:

load
[ID] as [ID2],
[Month Year Item] as [Month Year Item2zxy]
Resident [Download];
Right Join (CT2)
LOAD Max([Month Year Item2zxy]) as [Month Year Item2zxy]
Resident [CT2];

[CT2]:

LOAD
[Month Year Item],
FirstValue([ID]) as [ID]
RESIDENT [ Download]
group by
[Month Year Item],
[ID]
;
Right Join (CT2)
LOAD Max([Month Year Item])
Resident [CT2];

Re: Left Join to a max date

sunny_talwar — Mon, 30 Nov 2015 14:04:44 GMT

What exactly are you trying to do?