topic Re: Help me in below mapping scenario? in QlikView

Help me in below mapping scenario?

durgabhavani — Tue, 25 Aug 2015 03:13:54 GMT

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

Re: Help me in below mapping scenario?

jsingh71 — Tue, 25 Aug 2015 03:57:26 GMT

Hi Durga,

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

Re: Help me in below mapping scenario?

kaushiknsolanki — Tue, 25 Aug 2015 04:04:37 GMT

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

Re: Help me in below mapping scenario?

durgabhavani — Tue, 25 Aug 2015 04:26:49 GMT

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

Re: Help me in below mapping scenario?

kaushiknsolanki — Tue, 25 Aug 2015 05:30:15 GMT

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

Re: Help me in below mapping scenario?

sasiparupudi1 — Tue, 25 Aug 2015 06:15:10 GMT

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$]);

Re: Help me in below mapping scenario?

durgabhavani — Tue, 25 Aug 2015 08:37:36 GMT

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

Re: Help me in below mapping scenario?

sasiparupudi1 — Tue, 25 Aug 2015 08:44:15 GMT

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$]);

Re: Help me in below mapping scenario?

durgabhavani — Tue, 25 Aug 2015 09:03:46 GMT

sasi,

Brilliant! Its working.

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

Thanks,

Durga

Re: Help me in below mapping scenario?

sasiparupudi1 — Tue, 25 Aug 2015 09:13:29 GMT

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$]);

Re: Help me in below mapping scenario?

durgabhavani — Tue, 25 Aug 2015 09:36:23 GMT

Sasi,

latest code is bit confusion to me. can you share with my application.

thanks,

durga

Re: Help me in below mapping scenario?

sasiparupudi1 — Tue, 25 Aug 2015 09:40:37 GMT

Please check the attachemnt.. hope it works as I have not tested it

Sasi