How to replicate data over dimension values ?

[shekhar_analyti]

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

[sunny_talwar]

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;