Help me in below mapping scenario?

[durgabhavani]

Hi All,

Please help me in mapping the text file to replace the old customer id's with new customer id's

I have customer table having two fields.

Customer:

Customer_Number

Country

I have one mapping file with two fields.

Mapping:

Old_Customer_Number, (this customer number is from customer table.)

New_Customer_Number (this new customer number needs to update the old customer number in customer table)

My target is to update the new customer number from mapping file to customer number in customer table in dashboard.

Attached the application for reference.

Let me know if anything else needed?

Thanks,

Durga

[jsingh71]

Hi Durga,

Can you attach some sample data. I am using personal edition so not able to open the attached qvw.

[kaushiknsolanki]

Hi,

Try this Script.

Mapping:

Mapping LOAD Old_Customer_Number,

     New_Customer_Number

FROM

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

Customer:

LOAD CUSTOMER_NUMBER as Original_Number,

     Applymap('Mapping',CUSTOMER_NUMBER) as New_CUSTOMER_NUMBER,

     Country

FROM

(ooxml, embedded labels, table is Sheet1);

Regards,

Kaushik Solanki

[durgabhavani]

Thanks kaushik. Is there any chance to use joins to update the old customer id's.

[kaushiknsolanki]

Hi,

You can use the joins too, but it is recommended that you use mapping table in your scenario, cause joins in qlikview are resource intensive.

Regards,

Kaushik Solanki

[sasiparupudi1]

Try like this

Mapping:

mapping LOAD

    Old_Customer_Number,

    New_Customer_Number

FROM

[Mapping File.xls]

(biff, embedded labels, table is [Sheet1$]);

Customer:

LOAD  CUSTOMER_NUMBER,ApplyMap('Mapping', CUSTOMER_NUMBER,'NA') as  NEW_CUSTOMER_NUMBER,

    Country

FROM

[Customer Data.xls]

(biff, embedded labels, table is [Sheet1$]);

[durgabhavani]

Hi Sasi,

it gives perfect output. but i need to show old customer id of remaining countries instead of NA. That fulfills my requirement.

Please advise.

Thanks,

Durga

[sasiparupudi1 · Accepted solution]

Please try

Mapping:

mapping LOAD

    Old_Customer_Number,

    New_Customer_Number

FROM

[Mapping File.xls]

(biff, embedded labels, table is [Sheet1$]);

Customer:

LOAD  CUSTOMER_NUMBER,  ApplyMap('Mapping', CUSTOMER_NUMBER,CUSTOMER_NUMBER) as  NEW_CUSTOMER_NUMBER,

    Country

FROM

[Customer Data.xls]

(biff, embedded labels, table is [Sheet1$]);

[durgabhavani]

sasi,

Brilliant! Its working.

is it possible to display the old singapore data on my dashboard through list box?

Thanks,

Durga

[sasiparupudi1]

Please try

CustMap:

LOAD

    Old_Customer_Number,

    New_Customer_Number

FROM

[Mapping File.xls]

(biff, embedded labels, table is [Sheet1$]);

Mapping:

mapping LOAD

    Old_Customer_Number ,

    New_Customer_Number Resident CustMap;

OldCustomer:

LOAD Old_Customer_Number   Resident CustMap;

drop Table CustMap;

Customer:

LOAD  CUSTOMER_NUMBER,  ApplyMap('Mapping', CUSTOMER_NUMBER,CUSTOMER_NUMBER) as  NEW_CUSTOMER_NUMBER,

      Country

FROM

[Customer Data.xls]

(biff, embedded labels, table is [Sheet1$]);