Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I am trying to create an additional field (through my load script) called "Latest Date", which gives me the max LoginDate for each ID. So if max LoginDate for ID 2168 is 14-02-2017, then this date should appear in every row where ID = 2168.
How can I achieve this?
Thanks in advance!
Here i need to ask if you have LoginDate in your table it self then try with
MainTable:
Load
ID,
LoginDate,
...
From Location;
Left Join(MainTable)
Load
ID,
DATE(MAX(LoginDate)) as MaxLoginDate
Resident MainTable
Group By ID;
May be this
Fact:
LOAD ID,
Date,
OtherFields
FROM ...
Left Join (Fact)
LOAD ID,
Max(Date) as [Latest Date]
Resident Fact
Group By ID;
May be this?
Left join to fact
Load *, Max(Date) as MaxDate
Resident Fact
Group By <Non Aggr Dimensions>;
Here i need to ask if you have LoginDate in your table it self then try with
MainTable:
Load
ID,
LoginDate,
...
From Location;
Left Join(MainTable)
Load
ID,
DATE(MAX(LoginDate)) as MaxLoginDate
Resident MainTable
Group By ID;
Thanks guys! All three answers were very helpful
In the end Anand came closest to the solution which I could use directly in my code, so I will mark his answer as correct.
Thanks again.