Left Join to a max date

[tinkerz1]

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
;

[PrashantSangle]

Hi,

if you want latest ID then use FirstSortedValue()

Regards

[sunny_talwar]

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;

[Anonymous]

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

[jyothish8807]

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

KC

[tinkerz1]

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

[sunny_talwar]

What exactly are you trying to do?