topic Re: How to transfer Row wise Data into column wise in QlikView

How to transfer Row wise Data into column wise

pra_kale — Sat, 27 May 2017 05:05:29 GMT

Hi,

I have attached one excel file in which there are 2 Sheets.

In Query sheet i have give one sample data in which few policies are having two different Sas_code and Sas_Type because of which they are repeated. Below given is the e.g.

Policy_No	Premium	Issuance Date	Proposal Date	Sas_Code	Sas_Type	Amt
1234567	23000	12-Oct-15	10-Oct-15	LP	PS	10000
1234567	23000	12-Oct-15	10-Oct-15	LN	US	23000

In Expected out_put sheet i have given the expected out_put should be. Below given is the expected out_put e.g..

Policy_No	Premium	Issuance Date	Proposal Date	Sas_Code_LP	Sas_Type_PS	Amt_LP_PS	Sas_Code_LN	Sas_Type_US	Amt_LP_PS
1234567	23000	12-Oct-15	10-Oct-15	LP	PS	10000	LN	US	23000

I have tried it at front-end level, but i failed to get the desired out-put.

Help is required to get the expected out_put.

Thanks in Advance.

Re: How to transfer Row wise Data into column wise

Kushal_Chawda — Sat, 27 May 2017 05:20:08 GMT

Use below expressions

Sas_Type_LP

=Only({<Sas_Code={'LP'}>}Sas_Code)

Sas_Type_PS

=Only({<Sas_Code={'LP'}>}Sas_Type)

Amt_LP_PS

=sum({<Sas_Code={'LP'}>}Amt)

Sas_Code_LN

=Only({<Sas_Code={'LN'}>}Sas_Code)

Sas_Type_US

=Only({<Sas_Code={'US'}>}Sas_Type)

Amt_LP_PS

=sum({<Sas_Code={'LN'}>}Amt)

Re: How to transfer Row wise Data into column wise

pra_kale — Sat, 27 May 2017 05:36:11 GMT

Thanks Kushal for your help. It is working.

But, in data I have few policies which are not having any value under Sas_code = LP or LN and Sas_Type = PS or US and those policies are missed out from the out_put. But I want those policies are also to be get included. How i can do this.

Secondly, actual data is almost more than 2 lacs records with more than 20 columns. so if i handle this thing at front end level then transfer this out-put into excel may take lot of time. So whether it is possible we can handle this thing at script level.

Re: How to transfer Row wise Data into column wise

Kushal_Chawda — Sat, 27 May 2017 07:27:49 GMT

Data:
LOAD Policy_No, 
     Premium, 
     [Issuance Date], 
     [Proposal Date],     
     Sas_Code, 
     Sas_Type, 
     Amt
FROM
[Row wise Data.xlsx]
(ooxml, embedded labels, table is Query_data);

New: // Key as Policy_No, [Issuance Date],[Proposal Date],Premium
LOAD Policy_No, 
     [Issuance Date], 
     [Proposal Date], 
     Premium,
     'LP' as Sas_Code_LP,
     'PS' as Sas_Type_PS,
      Amt as Amt_LP_PS
Resident Data
where Sas_Code='LP';

Left Join(New)
LOAD Policy_No, 
     [Issuance Date], 
     [Proposal Date], 
     Premium,
     'LN' as Sas_Code_LN,
     'US' as Sas_Type_US,
      Amt as Amt_LN_US
Resident Data
where Sas_Code='LN';

Concatenate(New)
LOAD Policy_No, 
     [Issuance Date], 
     [Proposal Date],
     Premium, 
     '' as Sas_Code_LP,
     '' as Sas_Type_PS,
     '' as Amt_LP_PS,
     '' as Sas_Code_LN,
     '' as Sas_Type_US,
     '' as Amt_LN_US
Resident Data
where len(trim(Sas_Code))=0 and len(trim(Sas_Type))=0;


DROP Table Data;

Re: How to transfer Row wise Data into column wise

Kushal_Chawda — Sat, 27 May 2017 07:56:38 GMT

or this

Data:
LOAD Policy_No, 
     Premium, 
     [Issuance Date], 
     [Proposal Date], 
     AutoNumber(Policy_No&'|'&Premium&'|'&[Issuance Date]&'|'&[Proposal Date]) as Key,
     Sas_Code, 
     Sas_Type, 
     Amt
FROM

(ooxml, embedded labels, table is Query_data);


Left Join(Data) // Key as Policy_No, [Issuance Date],[Proposal Date],Premium
LOAD Key,
     'LP' as Sas_Code_LP,
     'PS' as Sas_Type_PS,
      Amt as Amt_LP_PS
Resident Data
where Sas_Code='LP';

Left Join(Data)
LOAD Key,
     'LN' as Sas_Code_LN,
     'US' as Sas_Type_US,
      Amt as Amt_LN_US
Resident Data
where Sas_Code='LN';

Final:
NoConcatenate
LOAD Distinct Policy_No, 
     [Issuance Date], 
     [Proposal Date], 
     Premium,
     Sas_Code_LN,
     Sas_Type_US,
     Amt_LN_US,
     Sas_Code_LP,
     Sas_Type_PS,
     Amt_LP_PS
Resident Data;

DROP Table Data;

Update : Created a key to make join efficient

Re: How to transfer Row wise Data into column wise

pra_kale — Sat, 27 May 2017 09:13:49 GMT

Great Kushal...it is working fine.

Thank you very much for your Help....

Re: How to transfer Row wise Data into column wise

Kushal_Chawda — Sat, 27 May 2017 09:23:16 GMT

Glad to help. Only one suggestion is that, you can remove Premium from the key

Re: How to transfer Row wise Data into column wise

pra_kale — Sat, 27 May 2017 11:28:41 GMT

Ok Thanks Kushal.