Qlik Community

Ask a Question

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

QVD file not loading when combined with mapping tables

Hi,

In data model script, I created two Mapping tables:

1)    ProdColorPrimary:

          MAPPING LOAD

          %PRODUCT_KEY, RGB(Red,Green.Blue)

          from ..\ProdColorPrimary.xlsx

2)     ProdColorSecondary:

         MAPPING LOAD

         %PRODUCT_KEY, RGB(Red,Green.Blue)

         from ..\ProdColorSecondary.xlsx

Then I tried to loaded PRODUCT.QVD file with new field (%PRODUCT_KEY) from this mapping table following syntax given below:

3)      PRODUCT:

          LOAD DISTINCT

          DW_PRODUCT_FAMILY_ID as %PRODUCT_KEY,

          MARKET_NAME AS [Market], 

          AT_PROMO_FLG,

          DISPLAY_PRODUCT_GROUP_NAME AS Product,

          applymap('ProdColorPrimary',%PRODUCT_KEY,RGB(128,128,128)) as PrimaryColor,
          applymap('ProdColorSecondary',%PRODUCT_KEY,RGB(0,128,255)) as SecondaryColor


          FROM
          Data\PRODUCT.qvd (qvd)
          WHERE exists(%PRODUCT_KEY,DW_PRODUCT_FAMILY_ID)

While loading these files, I below following error.

[ Error:

Field not found - <%PRODUCT_KEY>

PRODUCT:

 

          LOAD DISTINCT

          DW_PRODUCT_FAMILY_ID as %PRODUCT_KEY,

          MARKET_NAME AS [Market], 

          AT_PROMO_FLG,

          DISPLAY_PRODUCT_GROUP_NAME AS Product,

          applymap('ProdColorPrimary', %PRODUCT_KEY, RGB(128,128,128)) as PrimaryColor,
          applymap('ProdColorSecondary', %PRODUCT_KEY, RGB(0,128,255)) as SecondaryColor


          FROM
          Data\PRODUCT.qvd (qvd)
          WHERE exists(%PRODUCT_KEY,DW_PRODUCT_FAMILY_ID)

]

The Product table is not loaded in application. In my mapping tables I have %PRODUCT_KEY present only for products which has specific colors defined. The colors for which default colors needs to be used is not defined in these mapping tables.

Kindly help me in understanding the issue.

Does commenting WHERE exists(%PRODUCT_KEY,DW_PRODUCT_FAMILY_ID) would help?

Please suggest.

Thanks !


1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Hi,

    

Try this.

PRODUCT:

          LOAD DISTINCT

          DW_PRODUCT_FAMILY_ID as %PRODUCT_KEY,

          MARKET_NAME AS [Market],

          AT_PROMO_FLG,

          DISPLAY_PRODUCT_GROUP_NAME AS Product,

          applymap('ProdColorPrimary',DW_PRODUCT_FAMILY_ID,RGB(128,128,128)) as PrimaryColor,
          applymap('ProdColorSecondary',DW_PRODUCT_FAMILY_ID,RGB(0,128,255)) as SecondaryColor


          FROM
          Data\PRODUCT.qvd (qvd)
          WHERE exists(%PRODUCT_KEY,DW_PRODUCT_FAMILY_ID)


        You can not use the new field name in same load statement, so use the original field name.

Regards,

Kaushik Solanki

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".

View solution in original post

1 Reply
MVP & Luminary
MVP & Luminary

Hi,

    

Try this.

PRODUCT:

          LOAD DISTINCT

          DW_PRODUCT_FAMILY_ID as %PRODUCT_KEY,

          MARKET_NAME AS [Market],

          AT_PROMO_FLG,

          DISPLAY_PRODUCT_GROUP_NAME AS Product,

          applymap('ProdColorPrimary',DW_PRODUCT_FAMILY_ID,RGB(128,128,128)) as PrimaryColor,
          applymap('ProdColorSecondary',DW_PRODUCT_FAMILY_ID,RGB(0,128,255)) as SecondaryColor


          FROM
          Data\PRODUCT.qvd (qvd)
          WHERE exists(%PRODUCT_KEY,DW_PRODUCT_FAMILY_ID)


        You can not use the new field name in same load statement, so use the original field name.

Regards,

Kaushik Solanki

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".

View solution in original post