topic How to replicate data over dimension values ? in QlikView

How to replicate data over dimension values ?

shekhar_analyti — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: How to replicate data over dimension values ?

shekhar_analyti — Mon, 21 Jan 2019 16:11:17 GMT

Above is expected output

Re: How to replicate data over dimension values ?

shekhar_analyti — Mon, 21 Jan 2019 16:32:06 GMT

Sample

Re: How to replicate data over dimension values ?

shekhar_analyti — Mon, 21 Jan 2019 16:42:45 GMT

@sunny_talwar

Sunny Bhai . Please have a look .

Re: How to replicate data over dimension values ?

sunny_talwar — Tue, 22 Jan 2019 12:21:17 GMT

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

Re: How to replicate data over dimension values ?

shekhar_analyti — Tue, 22 Jan 2019 14:43:01 GMT

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

Re: How to replicate data over dimension values ?

sunny_talwar — Tue, 22 Jan 2019 15:10:03 GMT

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))

Re: How to replicate data over dimension values ?

shekhar_analyti — Tue, 22 Jan 2019 16:11:31 GMT

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

Re: How to replicate data over dimension values ?

sunny_talwar — Tue, 22 Jan 2019 16:16:24 GMT

@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?

Re: How to replicate data over dimension values ?

shekhar_analyti — Tue, 22 Jan 2019 17:06:21 GMT

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	-

Re: How to replicate data over dimension values ?

sunny_talwar — Tue, 22 Jan 2019 17:27:07 GMT

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;