Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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)
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.
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;
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
Great Kushal...it is working fine.
Thank you very much for your Help....
Glad to help. Only one suggestion is that, you can remove Premium from the key
Ok Thanks Kushal.