Script Help

[sona_sa]

Hi Team,

I have a scenerio, Like --> I have 2 country data and now I need to merge the both. Data is in this format.

Metric Country LOB Value
KPI 18 MALDIVES XLOB 1
KPI 21 MALDIVES XLOB PD
KPI 18 MALDIVES XLOB PD
   
Metric Country LOB VALUE
KPI 18 SRI LANKA XLOB PD
KPI 21 SRI LANKA XLOB PD
KPI 18 SRI LANKA XLOB ND

Output : Need Output on the behalf of these are the condition :
•If one country at LOB level has data and other country is PD, then treat PD as zero when adding together.
•If both countries at LOB level PD then combined PD
•If one country at LOB level PD and one Country ND then combined is ND.

OUTPUT   
Metric Country LOB VALUE
KPI 18 SRI LANKA & MALDIVES XLOB 0
KPI 21 SRI LANKA & MALDIVES  PD
KPI 18 SRI LANKA & MALDIVES  ND

So Can anyone look into the same and revert me.
Regards,
AS

[NW1965]

This script does the job, but just checking that I've got my assumptions right...

I assumed that the METRIC is the key to join the tables.

I assumed that the value of metric on line 3 is wrong, otherwise 4 countries are joined, and if that data is correct then I'm unsure how you would join the tables/countries together. I renamed the metric in line 3 on both samples as KPI 22.

This is the script (I'm sure there is a tidier way, but this works):

Data:
LOAD Metric,
Country AS FirstCountry,
LOB AS FirstLOB,
Value AS FirstValue
FROM
[Z:\TestData.xlsx]
(ooxml, embedded labels, table is Sheet1);


LEFT JOIN(Data)
LOAD Metric,
Country AS SecondCountry,
LOB AS SecondLOB,
Value AS SecondValue
FROM
[Z:\TestData.xlsx]
(ooxml, embedded labels, table is Sheet2);

FinalData:
NoConcatenate
LOAD Metric AS FinalMetric
, FirstCountry & ' & ' & SecondCountry AS Country
, if(FirstValue='PD' AND SecondValue='PD','',
if(FirstValue='ND' OR SecondValue='ND','',
FirstLOB)) AS LOB
, if(FirstValue='PD' AND SecondValue='PD','PD',
if(FirstValue='ND' OR SecondValue='ND','ND',
IF(ISNUM(FirstValue) AND NOT ISNUM(SecondValue),FirstValue,
IF(NOT ISNUM(FirstValue) AND ISNUM(SecondValue),SecondValue,
FirstValue + SecondValue)))) AS Value
RESIDENT Data;

This is an image of what I'm showing in Qlik:

The first image simply shows the state of the data table when they are joined, this can be removed in the script as it shouldn't be required.

[sona_sa · Accepted solution]

Hi, Thanks for response --> For More Easy to understand, Please find the attached xls.

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.

Lots of thanks in advance.

Regards,

AS