Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All ,
I want to replicate data row which is having Max value in a particular field with all distinct Dimension values in reach row .
Data
LOAD * INLINE [
Company, Year , Qtr, YQ_Num, Date, Area, Performance, Field1, Field2, Field3, Field4, Field5, Field6
Metallica, 2017, Q2, 20172, , A, 5, XXXXXY, XXXXXY, XXXXXY, XXXXXY, XXXXXY, XXXXXY
Metallica, 2017, Q1, 20171, , B, 6, XXXXXY, XXXXXY, XXXXXY, XXXXXY, XXXXXY, XXXXXY
Intel, 2016, Q2, 20162, , C, 2, XXXXXY, XXXXXY, XXXXXY, XXXXXY, XXXXXY, XXXXXY
Intel, 2015, Q4, 20154, , D, 7, XXXXXY, XXXXXY, XXXXXY, XXXXXY, XXXXXY, XXXXXY
Peter ENGLAND, 2017, Q4, 20174, , C, 1, XXXXXY, XXXXXY, XXXXXY, XXXXXY, XXXXXY, XXXXXY
Peter PUNJAB, 2014, Q2, 20142, , A, 6, XXXXXY, XXXXXY, XXXXXY, XXXXXY, XXXXXY, XXXXXY
Peter PUNJAB, 2013, Q1, 20131, , M, 5, XXXXXY, XXXXXY, XXXXXY, XXXXXY, XXXXXY, XXXXXY
Peter PUNJAB, 2014, Q2, 20143, , B, 5, XXXXXY, XXXXXY, XXXXXY, XXXXXY, XXXXXY, XXXXXY
];
Thanks & Regards
Shekar
It is good that you are eager to learn, but there are sometime things which are better to do one way or the other.... unless you really want to do this in the script... I would try to do it on the front end... but here is the script solution
Table: LOAD *, Hash128(Company&'|'&Area) as Key; LOAD * INLINE [ Company, Year, Qtr, YQ_Num, Area, Performance Metallica, 2017, Q2, 20172, A, 5 Metallica, 2017, Q1, 20171, B, 6 Intel, 2016, Q2, 20162, C, 2 Intel, 2015, Q4, 20154, D, 7 Peter ENGLAND, 2017, Q4, 20174, C, 1 Peter PUNJAB, 2014, Q2, 20142, A, 6 Peter PUNJAB, 2013, Q1, 20131, M, 5 Peter PUNJAB, 2014, Q2, 20143, B, 5 ]; TempTable: LOAD Company, FirstSortedValue(Year, -YQ_Num) as Year, FirstSortedValue(Qtr, -YQ_Num) as Qtr, Max(YQ_Num) as YQ_Num Resident Table Group By Company; Left Join (TempTable) LOAD DISTINCT Area Resident Table; Concatenate (Table) LOAD Company, Year, Qtr, YQ_Num, Area Resident TempTable Where not Exists(Key, Hash128(Company&'|'&Area)); DROP Table TempTable; FinalTable: NoConcatenate LOAD Company, Year, Qtr, YQ_Num, Area, If(Company <> Previous(Company), Performance) as Performance Resident Table Order By Company, YQ_Num desc, Performance desc; DROP Table Table;