Join Question

[#1]

Hi Qlik Community,

I need help getting UPC from this table Produpc.  A product can have multiple UPC codes (CASE, PACK, EACH), but we want "EACH" UPC to represent a Product.

Example:

Product      UoM     UPC

123ABC      CASE     1212121212

123ABC      PACK     3434343434

123ABC      EACH     5656565656

I would the each to replace CASE, PACK like below:

Product      UoM     UPC

123ABC      CASE     5656565656

123ABC      PACK     5656565656

123ABC      EACH     5656565656

Thanks for your help!

LIB CONNECT TO 'SVR-ITA-AFS';

LOAD prdventky,

    prdtky;

    //"prdum_tky";

SQL SELECT prdventky,

    prdtky

    //"prdum_tky"

FROM afserpprod.dbo.prodvendr;

Produmupc:

LOAD "um_upctky",

    //prdum_tky,

    //um_cod as umcode,

    prdtky  ;

SQL SELECT "um_upctky",

    //prdum_tky,

    um_cod ,

    prdtky

FROM afserpprod.dbo.produmupc

where um_cod='EA';

Produpc:

LOAD "um_upctky",

    stsflg as upc_stsflg,

    upc;

SQL SELECT "um_upctky",

    stsflg,

    upc

FROM afserpprod.dbo.produpc

where stsflg='A';

[sunny_talwar · Accepted solution]

How is the script provided below related to the headers of the sample? Can you point out which field is coming from which table?

In general you can try like this:

Table:

LOAD Product,

          UoM,

          UPC as UPC_Temp

FROM Source;

Left Join (Table)

LOAD Product,

          UPC_Temp as UPC

Resident Table

Where UoM = 'EACH';

DROP Field UPC_Temp;

[#3]

Thanks for your help Sunny.  This is from Crystals Report.