topic Re: Max Date-Left Join or Right join in App Development

Max Date-Left Join or Right join

niranjana — Wed, 06 Mar 2024 10:20:18 GMT

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

Re: Max Date-Left Join or Right join

WaltShpuntoff — Wed, 06 Mar 2024 13:57:22 GMT

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

Re: Max Date-Left Join or Right join

niranjana — Wed, 06 Mar 2024 15:28:58 GMT

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?

load claimno, max(modi date) as MaxMD

resident Full table group by claimno;

left join

FullTable

Thanks

Re: Max Date-Left Join or Right join

WaltShpuntoff — Wed, 06 Mar 2024 23:14:28 GMT

"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

Re: Max Date-Left Join or Right join

Pallav — Thu, 07 Mar 2024 04:46:10 GMT

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;