Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

amit_saini
Honored Contributor III

Mapping help???

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

7 Replies
sasiparupudi1
Honored Contributor III

Re: Mapping help???

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
Honored Contributor III

Re: Mapping help???

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
Honored Contributor III

Re: Mapping help???

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
Honored Contributor III

Re: Mapping help???

No synthetic key .

Tried agg separately also , no success.

jagansunindia

Any suggestions ????

Thanks,

AS

amit_saini
Honored Contributor III

Re: Mapping help???

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
Honored Contributor III

Re: Mapping help???

Hi Amit

Is it not may be because of AOP_Data_Flag,?

hth

Sasi

amit_saini
Honored Contributor III

Re: Mapping help???

Yes you are right.

Thanks,
AS

Community Browser