Re: Mapping help??? - Qlik Community

amit_saini · ‎2015-09-08

Hi Folks,

Below is my issue.

Reporting Unit_Name is not showing any related data for Platform_Name , CustomerHier01_Name and Vehicle_Name

Script:

UNQUALIFY *;

//start mapping load statements from here if any.

CustomerHier01_Mapping:

Mapping

LOAD

KUNNR as CustomerHier01,

NAME1

FROM

(qvd);

CustomerHier02_Mapping:

Mapping

LOAD

KUNNR as CustomerHier02,

NAME1

FROM

(qvd);

Continent_Mapping:

Mapping LOAD

WW001 as Continent,

BEZEK

FROM

(qvd)

WHERE SPRAS = 'E';

[PH - Automotive Y/N_Mapping]:

Mapping LOAD WWPH4 as [PH - Automotive Y/N],

BEZEK

FROM

(qvd)

WHERE SPRAS = 'E';

[PH - Type of Product_Mapping]:

Mapping LOAD

WWPH5 as [PH - Type of Product],

BEZEK

FROM

(qvd)

WHERE SPRAS = 'E';

[PH -Specific Product_Mapping]:

Mapping LOAD

WWPH6 as [PH -Specific Product],

BEZEK

FROM

(qvd)

WHERE SPRAS = 'E';

Platform_Mapping:

Mapping LOAD

WW004 as Platform,

BEZEK

FROM

(qvd)

WHERE SPRAS = 'E';

Vehicle_Mapping:

Mapping LOAD

WWM10 as Vehicle,

BEZEK

FROM

(qvd)

WHERE SPRAS = 'E';

[Reporting Unit_Mapping]:

Mapping LOAD

WW006 as [Reporting Unit],

BEZEK

FROM

(qvd)

WHERE SPRAS = 'E';

FOR EACH vPlant in '000', '002','003','004','008','011','022','400'

set vTab = 'CE42$(vPlant)';

CE42:

//CONCATENATE(CE42)

LOAD

PAOBJNR &'-'& $(vPlant) as %PartnerObjectNumber,

PAOBJNR as PAOBJNR_42,

'CE3242' as Table_Name,

//PERBL as [Period block/year],

//REC_WAERS as [Record Currency],

KMHI01 as CustomerHier01,

KMHI02 as CustomerHier02,

WW001 as Continent,

WW006 as [Reporting Unit],

WW004 as Platform,

WWM10 as Vehicle,

WWPH4 as [PH - Automotive Y/N],

WWPH5 as [PH - Type of Product],

WWPH6 as [PH -Specific Product]

from $(vTab).qvd(qvd)

WHERE NOT ISNULL(PAOBJNR) ;

NEXT;

FOR EACH vPlant in '000','002','003','004','008','011','022','400'

set vTab = 'CE32$(vPlant)';

CE32:

//CONCATENATE(CE32)

// JOIN

LOAD

PAOBJNR &'-'& $(vPlant) as %PartnerObjectNumber,

PAOBJNR as PAOBJNR_32,

IF(PLIKZ= 0 ,'Actual/FC','Budget') as Type,

'CE3242' as Table_Name,

PERBL as [Period block/year],

REC_WAERS as [Record Currency],

PLIKZ as AOP_Data_Flag,

VVS01001 as [Sales Price],

VVS02001 as [Pump price change],

VVS03001 as [Raw Mat price change],

VVS04001 as [Other comp. price ch],

VVS05001 as [Savings],

VVS12001 as [Price Retrobilling],

VV001001 as [Transp to suppl plnt],

VV002001 as [Transp to customer],

VV003001 as [Final Assembly rev.],

VV004001 as [Sequenzing revenue],

VVS13001 as [Price Changes],

VVS10001 as [Amortisation intern],

VVS14001 as [Order/Project Sales],

VVS06001 as [Cash Discount calc.],

VVS11001 as [Rebates],

VVS07001 as [Cash Discount actual],

VVM15001 as [ML Freight-in],

VVM01001 as [ML Packaging costs],

VVC20001 as [Sequenzing costs],

VVC21001 as [Freight-out],

VVM02001 as [ML Raw materials],

VVM03001 as [ML Pumps],

VVM04001 as [ML Fluor/Nitrogen],

VVM05001 as [ML oth. comp.- purch],

VVC07001 as [Internal Deliv. cost],

VVC30001 as [CoGS Mat/COMP/Pumps],

VVM09001 as [ML Direct Labor -var],

VVM10001 as [ML Contract labor],

VVM12001 as [ML mach. cost - var],

VVC01001 as [Freight-in],

VVC02001 as [Packaging costs],

//VVC20001 as [Sequenzing costs],

//VVC21001 as [Freight-out],

ROHST001 as [Raw materials],

VVC04001 as [Pumps],

VVC05001 as [Fluor/Nitrogen],

VVC06001 as [Other comp.- purchas],

//VVC07001 as [Internal Deliv. cost],

//VVC30001 as [CoGS Mat/COMP/Pumps],

VVC08001 as [Direct Labor (var.)],

VVC09001 as [Contract labor],

VVC10001 as [Machine cost (var.)],

IF( PLIKZ = 0,

VVS01001

+VVS02001

+VVS03001

+VVS04001

+VVS05001

+VVS12001

+VV001001

+VV002001

+VV003001

+VV004001

+VVS13001

+VVS10001

+VVS14001

-VVS06001

+VVS11001

+VVS07001,0)

as ACTUAL_SALES,

IF( PLIKZ = 1,

VVS01001

+VVS02001

+VVS03001

+VVS04001

+VVS05001

+VVS12001

+VV001001

+VV002001

+VV003001

+VV004001

+VVS13001

+VVS10001

+VVS14001

-VVS06001

+VVS11001

+VVS07001,0)

as PLAN_SALES,

IF( PLIKZ = 0,

VVS01001

+VVS02001

+VVS03001

+VVS04001

+VVS05001

+VVS12001

+VV001001

+VV002001

+VV003001

+VV004001

+VVS13001

+VVS10001

+VVS14001

-VVS06001

+VVS11001

+VVS07001

-VVM15001

- VVM01001

- VVC20001

- VVC21001

- VVM02001

- VVM03001

- VVM04001

- VVM05001

- VVC07001

//- VVC30001

- VVM09001

- VVM10001

- VVM12001,0)

as ACTUAL_MARGIN,

IF( PLIKZ = 1,

VVS01001

+VVS02001

+VVS03001

+VVS04001

+VVS05001

+VVS12001

+VV001001

+VV002001

+VV003001

+VV004001

+VVS13001

+VVS10001

+VVS14001

-VVS06001

+VVS11001

+VVS07001

-VVC01001

-VVC02001

-VVC20001

-VVC21001

-ROHST001

-VVC04001

-VVC05001

-VVC06001

-VVC07001

-VVC30001

-VVC08001

-VVC09001

-VVC10001)

as PLAN_MARGIN,

IF( PLIKZ = 0,

( (

VVS01001

+VVS02001

+VVS03001

+VVS04001

+VVS05001

+VVS12001

+VV001001

+VV002001

+VV003001

+VV004001

+VVS13001

+VVS10001

+VVS14001

-VVS06001

+VVS11001

+VVS07001

- VVM15001

- VVM01001

- VVC20001

- VVC21001

- VVM02001

- VVM03001

- VVM04001

- VVM05001

- VVC07001

//- VVC30001

- VVM09001

- VVM10001

- VVM12001) /

( VVS01001

+VVS02001

+VVS03001

+VVS04001

+VVS05001

+VVS12001

+VV001001

+VV002001

+VV003001

+VV004001

+VVS13001

+VVS10001

+VVS14001

-VVS06001

+VVS11001

+VVS07001)) ,0)

as [Margin ACTUAL (% of Sales)],

IF( PLIKZ = 1,

( (

VVS01001

+VVS02001

+VVS03001

+VVS04001

+VVS05001

+VVS12001

+VV001001

+VV002001

+VV003001

+VV004001

+VVS13001

+VVS10001

+VVS14001

-VVS06001

+VVS11001

+VVS07001

-VVC01001

-VVC02001

-VVC20001

-VVC21001

-ROHST001

-VVC04001

-VVC05001

-VVC06001

-VVC07001

-VVC30001

-VVC08001

-VVC09001

-VVC10001

) /

( VVS01001

+VVS02001

+VVS03001

+VVS04001

+VVS05001

+VVS12001

+VV001001

+VV002001

+VV003001

+VV004001

+VVS13001

+VVS10001

+VVS14001

-VVS06001

+VVS11001

+VVS07001)) ,0)

as [Margin Plan (% of Sales)]

from $(vTab).qvd(qvd)

WHERE NOT ISNULL(PAOBJNR) ;

NEXT;

//exit script;

//DROP FIELD dummy_42;

Join (CE42)

LOAD *

RESIDENT CE32;

drop table CE32;

CE_All_TEMP:

LOAD

//[Period block/year],

//[Record Currency],

//AOP_Data_Flag,

//SUM(ACTUAL_SALES) as ACTUAL_SALES,

//SUM(PLAN_SALES) as PLAN_SALES,

//SUM(ACTUAL_MARGIN) as ACTUAL_MARGIN,

//SUM(PLAN_MARGIN) as PLAN_MARGIN,

//SUM([Margin ACTUAL (% of Sales)]) as [Margin ACTUAL (% of Sales)],

//SUM ([Margin Plan (% of Sales)]) as [Margin Plan (% of Sales)],

*,

[Record Currency]&'-'& NUM#(right([Period block/year],2),'#') as %XRATE_KEY1,

[Record Currency]&'-'& NUM#(right(left([Period block/year],4),2),'#') as %XRATE_KEY2,

[Record Currency]&'-'& NUM#(right([Period block/year],2),'#') as %XRATE_KEY1_SAP,

[Record Currency]&'-'& NUM#(right(left([Period block/year],4),2),'#') as %XRATE_KEY2_SAP,

CustomerHier01 &'-'& APPLYMAP('CustomerHier01_Mapping',CustomerHier01) as CustomerHier01_Name,

CustomerHier02 &'-'& APPLYMAP('CustomerHier02_Mapping',CustomerHier02) as CustomerHier02_Name,

Continent &'-'& APPLYMAP('Continent_Mapping',Continent) as Continent_Name,

[PH - Automotive Y/N] &'-'& APPLYMAP('PH - Automotive Y/N_Mapping',[PH - Automotive Y/N]) as [PH - Automotive Y/N_Name],

[PH - Type of Product] &'-'& APPLYMAP('PH - Type of Product_Mapping',[PH - Type of Product]) as [PH - Type of Product_Name],

[PH -Specific Product] &'-'& APPLYMAP('PH -Specific Product_Mapping',[PH -Specific Product]) as [PH -Specific Product_Name],

Platform &'-'& APPLYMAP('Platform_Mapping',Platform) as Platform_Name,

[Reporting Unit] &'-'& APPLYMAP('Reporting Unit_Mapping',[Reporting Unit]) as [Reporting Unit_Name],

Vehicle &'-'& APPLYMAP('Vehicle_Mapping',Vehicle) as [Vehicle_Name]

RESIDENT CE42

;

DROP TABLE CE42;

DROP FIELD CustomerHier01, CustomerHier02, Continent ,[PH - Automotive Y/N] ,[PH - Type of Product] ,[PH -Specific Product],Platform ,[Reporting Unit] ,Vehicle;

//QUALIFY *;

CE_All:

//A:

LOAD

CustomerHier01_Name,

CustomerHier02_Name,

//'CustomerHier' as Dimention,

%XRATE_KEY1,

%XRATE_KEY2,

CustomerHier01_Name & '-' & CustomerHier02_Name as Dimention,

'CustomerHier01_Name - CustomerHier02_Name' as Dimention_Type,

//[Period block/year],

//[Record Currency],

//AOP_Data_Flag,

SUM(ACTUAL_SALES) as ACTUAL_SALES,

SUM(PLAN_SALES) as PLAN_SALES,

SUM(ACTUAL_MARGIN) as ACTUAL_MARGIN,

SUM(PLAN_MARGIN) as PLAN_MARGIN,

SUM([Margin ACTUAL (% of Sales)]) as [Margin ACTUAL (% of Sales)],

SUM ([Margin Plan (% of Sales)]) as [Margin Plan (% of Sales)]

RESIDENT CE_All_TEMP

GROUP BY CustomerHier01_Name,CustomerHier02_Name,%XRATE_KEY2, %XRATE_KEY1;

CONCATENATE (CE_All)

LOAD

Continent_Name,

[Reporting Unit_Name],

%XRATE_KEY1,

%XRATE_KEY2,

Continent_Name &'-'& [Reporting Unit_Name] as Dimention,

'Continent_Name - Reporting Unit_Name' as Dimention_Type,

//[Period block/year],

//[Record Currency],

//AOP_Data_Flag,

SUM(ACTUAL_SALES) as ACTUAL_SALES,

SUM(PLAN_SALES) as PLAN_SALES,

SUM(ACTUAL_MARGIN) as ACTUAL_MARGIN,

SUM(PLAN_MARGIN) as PLAN_MARGIN,

SUM([Margin ACTUAL (% of Sales)]) as [Margin ACTUAL (% of Sales)],

SUM ([Margin Plan (% of Sales)]) as [Margin Plan (% of Sales)]

RESIDENT CE_All_TEMP

GROUP BY Continent_Name,[Reporting Unit_Name],%XRATE_KEY2, %XRATE_KEY1;

CONCATENATE (CE_All)

LOAD

[PH - Automotive Y/N_Name],

[PH - Type of Product_Name],

[PH -Specific Product_Name],

%XRATE_KEY1,

%XRATE_KEY2,

[PH - Automotive Y/N_Name] & '-' & [PH - Type of Product_Name] & '-' & [PH -Specific Product_Name] as Dimention,

'PH - Automotive Y/N_Name - PH - Type of Product_Name - PH -Specific Product_Name' as Dimention_Type,

//[Period block/year],

//[Record Currency],

//AOP_Data_Flag,

SUM(ACTUAL_SALES) as ACTUAL_SALES,

SUM(PLAN_SALES) as PLAN_SALES,

SUM(ACTUAL_MARGIN) as ACTUAL_MARGIN,

SUM(PLAN_MARGIN) as PLAN_MARGIN,

SUM([Margin ACTUAL (% of Sales)]) as [Margin ACTUAL (% of Sales)],

SUM ([Margin Plan (% of Sales)]) as [Margin Plan (% of Sales)]

RESIDENT CE_All_TEMP

GROUP BY [PH - Automotive Y/N_Name],

[PH - Type of Product_Name],

[PH -Specific Product_Name],%XRATE_KEY2, %XRATE_KEY1;

CONCATENATE (CE_All)

LOAD

Platform_Name,

%XRATE_KEY1,

%XRATE_KEY2,

Platform_Name as Dimention,

'Platform_Name' as Dimention_Type,

//[Period block/year],

//[Record Currency],

//AOP_Data_Flag,

SUM(ACTUAL_SALES) as ACTUAL_SALES,

SUM(PLAN_SALES) as PLAN_SALES,

SUM(ACTUAL_MARGIN) as ACTUAL_MARGIN,

SUM(PLAN_MARGIN) as PLAN_MARGIN,

SUM([Margin ACTUAL (% of Sales)]) as [Margin ACTUAL (% of Sales)],

SUM ([Margin Plan (% of Sales)]) as [Margin Plan (% of Sales)]

RESIDENT CE_All_TEMP

GROUP BY Platform_Name,%XRATE_KEY2, %XRATE_KEY1;

CONCATENATE (CE_All)

LOAD

[Vehicle_Name],

%XRATE_KEY1,

%XRATE_KEY2,

[Vehicle_Name] as Dimention,

'Vehicle_Name' as Dimention_Type,

//[Period block/year],

//[Record Currency],

//AOP_Data_Flag,

SUM(ACTUAL_SALES) as ACTUAL_SALES,

SUM(PLAN_SALES) as PLAN_SALES,

SUM(ACTUAL_MARGIN) as ACTUAL_MARGIN,

SUM(PLAN_MARGIN) as PLAN_MARGIN,

SUM([Margin ACTUAL (% of Sales)]) as [Margin ACTUAL (% of Sales)],

SUM ([Margin Plan (% of Sales)]) as [Margin Plan (% of Sales)]

RESIDENT CE_All_TEMP

GROUP BY [Vehicle_Name],%XRATE_KEY2, %XRATE_KEY1;

CE_All_xtraFields:

LEFT JOIN (CE_All)

LOAD

DISTINCT %XRATE_KEY1,

%XRATE_KEY2,

AOP_Data_Flag,

[Record Currency],

Type,

[Period block/year] as %YearMonth,

[Period block/year]

RESIDENT CE_All_TEMP

//GROUP BY %XRATE_KEY2, %XRATE_KEY1

;

DROP TABLE CE_All_TEMP;

Any Suggestion????

Thanks,

AS

sasiparupudi1 · ‎2015-09-08

Try putting the else part for your applymap function calls to see which map is not returning values

[Reporting Unit] &'-'& APPLYMAP('Reporting Unit_Mapping',[Reporting Unit],'No_Reporting Unit_Mapping') as as [Reporting Unit_Name]

repeat this to the other apply maps also

hth

Sasi

amit_saini · ‎2015-09-08

Sasidhar,

when creating the CE_All table we are concatenating different pre-aggregations in the script. Here we´re loosing the connection from the Reporting Unit to the Customer, Platform, etc.

But not sure why so???

Thanks,

AS

sasiparupudi1 · ‎2015-09-08

I think you are having synthetic keys in your data model.

Also try loading the aggregations separately and see if you can spot the issue?

hth

Sasi

amit_saini · ‎2015-09-09

No synthetic key .

Tried agg separately also , no success.

jagan‌ sunindia‌

Any suggestions ????

Thanks,

AS

amit_saini · ‎2015-09-10

Hi Sasidhar,

Just because of below table I'm losing mapping connection:

CE_All_xtraFields:

LEFT JOIN (CE_All)

LOAD

DISTINCT %XRATE_KEY1,

%XRATE_KEY2,

AOP_Data_Flag,

[Record Currency],

Type,

[Period block/year] as %YearMonth,

[Period block/year]

RESIDENT CE_All_TEMP

//GROUP BY %XRATE_KEY2, %XRATE_KEY1

;

DROP TABLE CE_All_TEMP;

Thanks,

AS

sasiparupudi1 · ‎2015-09-10

Hi Amit

Is it not may be because of AOP_Data_Flag,?

hth

Sasi

amit_saini · ‎2015-09-10

Yes you are right.

Thanks,
AS