Mapping help

[joshrussin]

Hey guys,

I can't seem to figure out how to perform this mapping.

Attached is the excel document I am trying to accomplish in Qlik.

On the eWM sheet, there is a column called "testing" it is yellow. I am trying to compare it to the ECC batch's, to find errors in our system. if it returns "#N/A" it is an error. I am trying to just show these rows on a table in Qlik.

Attached is what i have so far of this.

My script is this so far..

MappingTable:

Mapping

LOAD

    "Posting Date",

    Batch

FROM [lib://qlikid_joshrussin/ZB_Putaway.xlsx]

(ooxml, embedded labels, table is ECC);

LOAD

    "Actual Goods Receipt Date",

    num("Putaway Date (Act.)" - "Actual Goods Receipt Date") as [Lead Time eWM],

    ApplyMap('MappingTable', "Batch") as "Batch Comparison",

    "Putaway Date (Act.)",

    "Locked (Overall)",

    "Purchase Order",

etc...

[luismadriz]

Hi Joshua,

After you load ECC you could create a mapping table with this:

  ECCMap:

  Mapping Load  

            Batch as SearchValue,

            Batch as ReturnValue

  Resident ECC; 

You can then Drop Table ECC; if you don't need it anymore...

Then, when you load EWM you can add this field confirming if the batch exists:

ApplyMap('ECCMap', Batch, '#N/A') as Testing

I hope this helps

Cheers,

Luis

[luismadriz · Accepted solution]

The script looks like this:

ECC:

LOAD

    Batch,

    Material,

    "Material Description",

    "Storage Location",

    "Order",

    "Posting Date",

    "Purchase Order",

    "Name 1",

    Vendor,

    Quantity,

    Plant,

    "Movement Type",

    "Movement Type Text",

    "Material Document",

    "Material Doc.Item",

    "Unit of Entry",

    "Amount in LC",

    "Time of Entry",

    "User name",

    Reference

FROM [lib://AttachedFiles/metric final.xlsx]

(ooxml, embedded labels, table is ECC);

MappingTable:

Mapping

LOAD

    Batch,

    Batch

Resident ECC;

Drop Table ECC;

LOAD *,

     ApplyMap('MappingTable', "Batch",'#N/A') as "Batch Comparison";

LOAD

    "Actual Goods Receipt Date",

    "Putaway Date (Act.)",

    "lead time",

    "Locked (Overall)",

    "Purchase Order",

    Product,

etc.

[joshrussin]

This clears up how to use Mapping a lot. I appreciate it! Also, the other post works perfectly, thank you!