Max Date-Left Join or Right join

[niranjana]

Hi All,

I have a requirement where I have to get max date.

In the script, After  transformations and QVDs, In the final step, I have the chart with all the columns i.e., which has both fields.- ClaimNo and Modified date.

I have chart with 170 columns. I must get max (modified date)  for each ClaimNo. Both columns are in the chart. 

My need is to get max date for each claimno . Should I left join or right join.?

Thanks

Niranjana

[WaltShpuntoff]

First of all - 170 columns in a single chart???

If you want the information to display just in the chart, then you will need to drop out the modified date from it, and just use a measure of Max(modified date). You may have to use Date(Max(modified date)) if it converts it to a number.

If you are doing this in the data load then you would do something like this:

Left join (claimTableName)
Load claimno, max(modified date) as [Last Update] resident claimTableName group by claimno;

hth

-ws

[niranjana]

Hi,

You mean to say,

Full Table:

............

left join (Full Table)

load 

claimno,

max(modfied date) as MaxMD

resident Full Table

group by claim no;

IN the chart, in dimension, If I put MaxMD  I am not getting the unique rows for each claim no, but when I put in expression as you said, I got....Shouldn't left joining in the script give the correct result in dimension itself? Why put in expression?

Or maybe right join?

Or 

load claimno, max(modi date) as MaxMD

resident Full table group by claimno;

left join

FullTable

Thanks

[WaltShpuntoff]

"use a measure of Max(modified date)" not a dimension.

That should fix the chart.

If you want it in the data model, try it the way I outlined it and see the results.

hth

-ws

[Pallav]

Full_Table:

Load

ClaimNo,

Modified date

From path.qvd (Qvd);

New_Table:

Load

ClaimNo,

Max(Modified date) as Modified date

Resident Full_Table group by ClaimNo;

Drop Table Full_Table;