Load latest Jobcode field value

[Eshwar1]

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

Temp_tab1:

LOAD 

    Branch_code,

    job_start_date,

    Jobcode

From Tab.qvd;

NoConcatenate

Final:

Load

        Branch_code,

     Max(job_start_date) as Job_Start_Date

Resident Temp_tab1

Group by Branch_code;

above script is working fine as iam getting latest Job_Start_Date

i have another field Jobcode need to retrieve latest jobcode

in Final table added the Jobcode field and mention same in Group by clause also

after adding the Jobcode field in Table chart it is not showing latest jobcode

can you suggest how to achieve this

Thank you in advance

Eshwar

[HirisH_V7]

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.

[Eshwar1]

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.

[HirisH_V7]

Final:

Load Job_Code,

        Branch_code,

     Max(job_start_date) as Job_Start_Date

Resident Temp_tab1

Group by Branch_code,Job_Code;

[Eshwar1]

tried this code but not showing latest Jobcode

latest date is showing but after adding the jobcode in table its not showing latest jobcode

[HirisH_V7]

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.

[Digvijay_Singh]

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)

[HirisH_V7 · Accepted solution]

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;

[Eshwar1]

same script showing error while loading in my app

below is the error 

Note : above inline table load script is working

[HirisH_V7]

Pl refer my code, in where statement there is no max.