topic Re: Apply Map in QlikView

Apply Map

Wed, 16 Mar 2016 03:21:14 GMT

Hi Experts,

How to use Apply Map? Can anyone share sample app

Please find attached sample data

Sheet1- Product Name

Sheet2 - Product Name to Map with Name

Thanks

Re: Apply Map

Digvijay_Singh — Wed, 16 Mar 2016 03:38:33 GMT

Load Sheet 2 table;

Create new mapping table as -

Name_Map:

Mapping load

Product Name,

Name

Resident <Name of your Sheet2 Table Here>;

Then use this as below while loading Sheet1;

Load

Product ID,

Product Name,

Applymap('Name_Map',Product Name,'Unknown') as Name,

Start Date,

End Date

From <Sheet1>

You may change sequence in different ways but the whole idea is to load mapping table first using the already loaded table and use the info in subsequent loading as I used above. If No name corresponding to Product Name then 'Unknown' value will be loaded.

Re: Apply Map

settu_periasamy — Wed, 16 Mar 2016 03:46:57 GMT

Hi,

Directory;
MAP:
Mapping LOAD [Product name],
Name
FROM
[Apply Map.xlsx]
(ooxml, embedded labels, table is Sheet2);

Final:
LOAD [Product ID],
[Product name],
Date#([Start Date],'M/D/YYYY') as [Start Date],
Date#([End Date],'M/D/YYYY') as [End Date],
ApplyMap('MAP',[Product name],'Unknown') as Name
FROM
[Apply Map.xlsx]
(ooxml, embedded labels, table is Sheet1);

Re: Apply Map

tamilarasu — Wed, 16 Mar 2016 03:49:00 GMT

Hi,

Try like below,

ProductMapping:
Mapping LOAD [Product name], 
     Name
FROM
[Apply Map.xlsx]
(ooxml, embedded labels, table is Sheet2);

Product:
LOAD [Product ID], 
  [Product name],
     Applymap('ProductMapping',[Product name], null()) as FullName,
     [Start Date], 
     [End Date]
FROM
[Apply Map.xlsx]
(ooxml, embedded labels, table is Sheet1);

Re: Apply Map

jagan — Wed, 16 Mar 2016 03:54:34 GMT

Hi,

Try like this

ProductsMapping:

MAPPING LOAD [Product name],

Name

FROM

[Apply Map.xlsx]

(ooxml, embedded labels, table is Sheet2);

Data:

LOAD [Product ID],

[Product name] AS ActualProductName,

ApplyMap('ProductsMapping', [Product name], 'NA') AS [Product name],

[Start Date],

[End Date]

FROM

[Apply Map.xlsx]

(ooxml, embedded labels, table is Sheet1);

Regards,

Jagan.

Re: Apply Map

jagan — Wed, 16 Mar 2016 03:56:18 GMT

Hi,

You can also try join

Data:

LOAD [Product ID],

[Product name] AS ActualProductName,

[Product name],

[Start Date],

[End Date]

FROM

[Apply Map.xlsx]

(ooxml, embedded labels, table is Sheet1);

LEFT JOIN(Data)

LOAD [Product name],

Name

FROM

[Apply Map.xlsx]

(ooxml, embedded labels, table is Sheet2);

Re: Apply Map

Wed, 16 Mar 2016 05:29:04 GMT

Hi,

PFB

Product:

Mapping

LOAD [Product name],

Name

FROM

(ooxml, embedded labels, table is Sheet2);

Details:

LOAD [Product ID],

[Product name],

[Start Date],

[End Date],

ApplyMap('Product',[Product name],'NA') as ProdcutDesc

FROM

(ooxml, embedded labels, table is Sheet1);

Regards,

Sub2u444