Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

i want map product id but it is there in 2 tables how to map ? whether its possible or not?

map1:

mapping LOAD productid,

     description

FROM

M:\ONLYQLIKVIEW\DOCUMENTS\MAPPING\map.xlsx

(ooxml, embedded labels, table is product);

map2:

Mapping

LOAD productid,

     discount

    // promoDiscount

FROM

M:\ONLYQLIKVIEW\DOCUMENTS\MAPPING\map.xlsx

(ooxml, embedded labels, table is discount);

map3:

Mapping

LOAD customerid,

     [customer name]

FROM

M:\ONLYQLIKVIEW\DOCUMENTS\MAPPING\map.xlsx

(ooxml, embedded labels, table is customer);

tomap:

LOAD orderid,

ApplyMap ('map1','map2',productid) as pid,

ApplyMap ('map3',customerid) as cid,

     productid,

     customerid,

     gross_amount,

     orderDate

FROM

(ooxml, embedded labels, table is sales);

10 Replies
Highlighted
deepakk
Valued Contributor III

Re: i want map product id but it is there in 2 tables how to map ? whether its possible or not?

hi,

Join the first two tables into one like below

map1:

LOAD productid,

     description

FROM

M:\ONLYQLIKVIEW\DOCUMENTS\MAPPING\map.xlsx

(ooxml, embedded labels, table is product);

join

LOAD productid,

     discount

    // promoDiscount

FROM

M:\ONLYQLIKVIEW\DOCUMENTS\MAPPING\map.xlsx

(ooxml, embedded labels, table is discount);

Then using apply map to add the field

Regards

Deepak

Not applicable

Re: i want map product id but it is there in 2 tables how to map ? whether its possible or not?

In your example the MAP1 & MAP2 have the ProductID and if you have pid in your source data you can use Mulitple ApplyMaps statement to get the description & discount.

LOAD orderid, pid ,

ApplyMap ('map1',pid,'xx') as Description,

ApplyMap ('map2',pid,0) as discount,

ApplyMap ('map3',customerid) as cid,

     productid,

     customerid,

     gross_amount,

     orderDate

FROM

(ooxml, embedded labels, table is sales);

I am assuming you are looking the multiple fields

If you need to calculate the field based on the multiple apply maps like below:

MAP1:

MAPPING LOAD A, B SOURCE1;

MAP2:

MAPPING LOAD B,C SOURCE2;

In my source I have A field, but I need a C field, then use like below:

MAIN:

LOAD A,

          D,

          E

          ApplyMap('MAP2' , ApplyMap('MAP1',A,'X') , 'x') AS C

FROM SOURCE;

Not applicable

Re: i want map product id but it is there in 2 tables how to map ? whether its possible or not?

map1:

Mapping

LOAD productid,

     description

FROM

M:\ONLYQLIKVIEW\DOCUMENTS\MAPPING\map.xlsx

(ooxml, embedded labels, table is product);

join

Mapping

LOAD productid,

     discount

    // promoDiscount

FROM

M:\ONLYQLIKVIEW\DOCUMENTS\MAPPING\map.xlsx

(ooxml, embedded labels, table is discount);

if i write like this even giving error

map3:

Mapping

LOAD customerid,

     [customer name]

FROM

M:\ONLYQLIKVIEW\DOCUMENTS\MAPPING\map.xlsx

(ooxml, embedded labels, table is customer);

MVP
MVP

Re: i want map product id but it is there in 2 tables how to map ? whether its possible or not?

You can use below script...

map1:

mapping LOAD

  productid,

  description

FROM

M:\ONLYQLIKVIEW\DOCUMENTS\MAPPING\map.xlsx

(ooxml, embedded labels, table is product);

map2:

Mapping LOAD

  productid,

    discount

    // promoDiscount

FROM

M:\ONLYQLIKVIEW\DOCUMENTS\MAPPING\map.xlsx

(ooxml, embedded labels, table is discount);

map3:

Mapping

LOAD

  customerid,

    [customer name]

FROM

M:\ONLYQLIKVIEW\DOCUMENTS\MAPPING\map.xlsx

(ooxml, embedded labels, table is customer);

tomap:

LOAD

  orderid,

  ApplyMap ('map1','productid','Unknown') as description,

  ApplyMap ('map2',productid,'Unknown') as discount,

    ApplyMap('map3',customerid,'Unknown') as [customer name],

    productid,

    customerid,

    gross_amount,

    orderDate

FROM

(ooxml, embedded labels, table is sales);

Not applicable

Re: i want map product id but it is there in 2 tables how to map ? whether its possible or not?

i did same its giving result but way of script writing is correct or not

MVP
MVP

Re: i want map product id but it is there in 2 tables how to map ? whether its possible or not?

There is nothing wrong in applying three ApplyMap in your script.

In fact, it is better to use ApplyMap as the Mapping load Table is not being kept in RAM after script run.

Not applicable

Re: i want map product id but it is there in 2 tables how to map ? whether its possible or not?

thanks

MVP
MVP

Re: i want map product id but it is there in 2 tables how to map ? whether its possible or not?

if your problem solved, please close this thread by choosing correct answer so that other members can use it.

Thanks

Not applicable

Re: i want map product id but it is there in 2 tables how to map ? whether its possible or not?

i am new to this site pls tell me where is  close button