SCRIPT HELP FOR CONSOLIDATION of TWO COUNTRIES DATA

[sona_sa]

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

[rahulpawarb]

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;

[sona_sa]

Thanks for response.
A quick question : This is not filling the requirement for the same : Below is my point.
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

Thanks.