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
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))
Above is expected output
Sample
Before I give any suggestion, can you share what have you tried so far?
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
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 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?
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 | - |