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

[shekhar_analyti]

Above is expected output 

[shekhar_analyti]

Sample

[shekhar_analyti]

@sunny_talwar

Sunny Bhai .  Please have a look  .

[sunny_talwar]

Before I give any suggestion, can you share what have you tried so far? 

[shekhar_analyti]

Hi Sunny Bhai ,

Thanks for the reply . 

My actual problem was something else with the same data . In order to solve that problem i thought of replicating data through list of dimension values , About which i really have no idea how to do .

Let me explain 

Data 

LOAD * INLINE [

Company, Year , Qtr, YQ_Num, Date, 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
];

Chart 
Dimension - Company 
Exp1 - Max(YQ_Num)
Exp 2 - FirstSortedValue({<Area={'A'}>}Performance,-aggr(Max(YQ_Num),Company,YQ_Num))
Exp 3 - FirstSortedValue({<Area={'D'}>}Performance,-aggr(Max(YQ_Num),Company,YQ_Num))
Exp 4 - FirstSortedValue({<Area={'B'}>}Performance,-aggr(Max(YQ_Num),Company,YQ_Num))

Output which i am getting is 

Company	Max YQ FOR COMPANY	REGION A	REGION B	REGION C	REGION D	REGION M
Intel	20162	-	-	2	7	-
Metallica	20172	5	6	-	-	-
Peter ENGLAND	20174	-	-	1	-	-
Peter PUNJAB	20143	6	5	-	-	5

But Output is needed was 

Company	Max YQ FOR COMPANY	REGION A	REGION B	REGION C	REGION D	REGION M
Intel	20162	-	-	2	-	-
Metallica	20172	5	-	-	-	-
Peter ENGLAND	20174	-	-	1	-	-
Peter PUNJAB	20143	-	5	-	-	-

So i thought that if i take Max YQ_Num from company and replicate the data through Dimension , above expression will work fine and it will be a new learning for me 

[sunny_talwar · Accepted solution]

Try these expressions for Regions

Only({<Area={'A'}>}Aggr(If(YQ_Num = Max(TOTAL <Company> YQ_Num), Performance), Company, YQ_Num))

Only({<Area={'B'}>}Aggr(If(YQ_Num = Max(TOTAL <Company> YQ_Num), Performance), Company, YQ_Num))

Only({<Area={'C'}>}Aggr(If(YQ_Num = Max(TOTAL <Company> YQ_Num), Performance), Company, YQ_Num))

Only({<Area={'D'}>}Aggr(If(YQ_Num = Max(TOTAL <Company> YQ_Num), Performance), Company, YQ_Num))

Only({<Area={'M'}>}Aggr(If(YQ_Num = Max(TOTAL <Company> YQ_Num), Performance), Company, YQ_Num))

[shekhar_analyti]

Thank You Sunny Bhai ...
But please also tell me way to to replicate data over dimension values .. It will be a new learning for me .. Please .

Regards
Shekar

[sunny_talwar]

---

@shekhar_analyti wrote:
But please also tell me way to to replicate data over dimension values ..

---

I am not sure I understand what this even means? Replicate data over dimension values? Can you elaborate?

[shekhar_analyti]

Sorry for confusion , i was referring to get following output using script (replicate data over dimension values) .. NOT EXPRESSIONS .
Input

Company	Year	Qtr	YQ_Num	Date	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

 Output 

Company	Year	Qtr	YQ_Num	Date	Area	Performance
Metallica	2017	Q2	20172		A	5
Metallica	2017	Q1	20172		B	-
Metallica	2017	Q2	20172		C	-
Metallica	2017	Q2	20172		D	-
Metallica	2017	Q2	20172		M	-
Intel	2016	Q2	20162		A	-
Intel	2016	Q2	20162		B	-
Intel	2016	Q2	20162		C	2
Intel	2015	Q4	20162		D	-
Intel	2016	Q2	20162		M	-
Peter ENGLAND	2017	Q4	20174		A	-
Peter ENGLAND	2017	Q4	20174		B	-
Peter ENGLAND	2017	Q4	20174		C	1
Peter ENGLAND	2017	Q4	20174		D	-
Peter ENGLAND	2017	Q4	20174		M	-
Peter PUNJAB	2014	Q2	20143		A	-
Peter PUNJAB	2014	Q2	20143		B	5
Peter PUNJAB	2014	Q2	20143		C	-
Peter PUNJAB	2014	Q2	20143		D	-
Peter PUNJAB	2014	Q2	20143		M	-