Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
i am trying to create a table chart with following fields
Branch_code, job_start_date, Jobcode
iam able to achieve latest job_start_date with using below script
Data:
Load * inline [
Branch_code, Job_Start_Date, Jobcode
116, 5/27/2024, ocd
116, 4/20/2024, bcs
116, 4/15/2024, hkj
];
Map_Final:
Mapping Load
Branch_code&'|'&
Date(Max(Date#(Job_Start_Date,'MM/DD/YYYY'))) as Key,
1 as 1
Resident Data
Group by Branch_code;
NoConcatenate
Final:
Load * Resident Data Where Applymap('Map_Final',Branch_code&'|'&Date(Date#(Job_Start_Date,'MM/DD/YYYY')))=1;
DROP Table Data;
Do you need Lastest Date for both Job Code and Branch Code seperately? if so need to call individual group by and create two seperate tables for same.
Else, do explain with sample data and required output.
Thank you for response Hirish
Out Put table like Branch_code, Latest_Job_Start_Date, Latest_Jobcode
1 branch_code have multiple dates and jobcodes
from that i need to display latest date and jobcode
Sample data
Load * inline
Branch_code, Job_Start_Date, Jobcode
116, 5/27/2024, ocd
116, 4/20/2024, bcs
116, 4/15/2024, hkj
];
i need to display in table only 116, 5/27/2024, ocd
can u suggest how to achieve.
tried this code but not showing latest Jobcode
latest date is showing but after adding the jobcode in table its not showing latest jobcode
As data contains three job code for same branch, when we do groupby it will give output of three lines. Could you elaborate more on why jobcode HKJ to be considered.
Try firstsortedvalue like below if it suits to your situation..
1.Measure 1 - FirstSortedValue(Job_Start_Date,-Job_Start_Date)
2. Measure 2 - firstsortedvalue(Branch_code,-Job_Start_Date)
3. Measure 3 - firstsortedvalue(Jobcode,-Job_Start_Date)
Data:
Load * inline [
Branch_code, Job_Start_Date, Jobcode
116, 5/27/2024, ocd
116, 4/20/2024, bcs
116, 4/15/2024, hkj
];
Map_Final:
Mapping Load
Branch_code&'|'&
Date(Max(Date#(Job_Start_Date,'MM/DD/YYYY'))) as Key,
1 as 1
Resident Data
Group by Branch_code;
NoConcatenate
Final:
Load * Resident Data Where Applymap('Map_Final',Branch_code&'|'&Date(Date#(Job_Start_Date,'MM/DD/YYYY')))=1;
DROP Table Data;
same script showing error while loading in my app
below is the error
Note : above inline table load script is working
Pl refer my code, in where statement there is no max.