Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
sona_sa
Creator II
Creator II

Script Help for Consolidate the 2 country

Hi Team,

I have a scenenrio, Like This --> In a table I have 2 country data with different ID, Month, LOB and Numerator (Value). Now I have to consolidate the data through script.

Sri Lanka Data + Maladive Data

1. If ID = KPI 18 and Month_Year = 201806 and Country = Sri Lanka and LOB = XLOB and Numerator = 1

and If ID = KPI 18 and Month_Year = 201806 and Country = Maldives and LOB = XLOB and Numerator = PD

Then Output =

ID = KPI 18 and Month_Years = 201806 and Country - Sri Lanka & Maldives and LOB = XLOB and Numerator = 1

2. If ID = KPI 18 and Month_Year = 201806 and Country = Sri Lanka and LOB = XLOB and Numerator = PD

and If ID = KPI 18 and Month_Year = 201806 and Country = Maldives and LOB = XLOB and Numerator = PD

Then Output =

ID = KPI 18 and Month_Years = 201806 and Country - Sri Lanka & Maldives and LOB = XLOB and Numerator = PD

3. If ID = KPI 18 and Month_Year = 201806 and Country = Sri Lanka and LOB = XLOB and Numerator = PD

and If ID = KPI 18 and Month_Year = 201806 and Country = Maldives and LOB = XLOB and Numerator = ND

Then Output =

ID = KPI 18 and Month_Years = 201806 and Country - Sri Lanka & Maldives and LOB = XLOB and Numerator = ND

For more ref. Please find the attached xls.

Please look into this and help me to solve the same.

I have kept in xls 2 ID, Suppose if it works for 18 and tomorrow if requirement change by ID, Like KPI 18 to KPI 21 then in this scenario what to do, I have to change by code by code or any other solution is there. You can check by KPI 18

Lots of thanks in advance.

Regards,

AS

1 Reply
rahulpawarb
Specialist III
Specialist III

May be this:

tmpData:
LOAD ID,
Month_Year,
Country,
LOB,
Numerator
FROM
[Country Consolidation.xlsx]
(ooxml, embedded labels, table is [SRI LANKA]);
Concatenate
LOAD ID,
Month_Year,
Country,
LOB,
Numerator
FROM
[Country Consolidation.xlsx]
(ooxml, embedded labels, table is MALDIVES);

NoConcatenate

Data:
LOAD
ID,
Month_Year,
If(Match(Country, 'MALDIVES', 'SRI LANKA'), 'Sri Lanka & Maldives', Country) AS Country,
LOB,
MinString(Numerator) AS Numerator
Resident tmpData
Group By ID,
Month_Year,
If(Match(Country, 'MALDIVES', 'SRI LANKA'), 'Sri Lanka & Maldives', Country),
LOB;

DROP TABLE tmpData;