topic Re: ApplyMap in QlikView

ApplyMap

dmxmikey — Wed, 25 Nov 2020 16:16:04 GMT

I have the below script and would like to output where vatcode is S, I need to output standard; vatcode Z I need to output zero, below is my current load script

Products:

Load [product]

FROM

\\appserver1\Qlikview\qlikviewdatafiles\qvds\Products.qvd

(qvd);

the current output is either S or Z

Re: ApplyMap

its_anandrjs — Mon, 11 Sep 2017 18:02:07 GMT

In Product field what are data items will is S and Z or you want to load only S and what do you mean by vatcode

Ex:-

Do this if i am understand

Products:

Load [product]

FROM

\\appserver1\Qlikview\qlikviewdatafiles\qvds\Products.qvd

(qvd) Where [product] = 'S';

Products:

Load [product]

FROM

\\appserver1\Qlikview\qlikviewdatafiles\qvds\Products.qvd

(qvd) Where Match( [product], 'S' );

Re: ApplyMap

trdandamudi — Mon, 11 Sep 2017 19:17:50 GMT

Not very clear what you need, May be you can try as below.... If this is not correct, can you please share a sample.

Products:

Load [product]

FROM

\\appserver1\Qlikview\qlikviewdatafiles\qvds\Products.qvd (qvd)

Where

vatcode='S';

Re: ApplyMap

dmxmikey — Mon, 11 Sep 2017 19:53:14 GMT

I need to output where vat is S I require word standard and where vat is Z I need word zero

Sent from my iPhone

Re: ApplyMap

trdandamudi — Mon, 11 Sep 2017 21:06:14 GMT

You can try as below:

Products:

Load [product],

If(Vat='S','Standard',
If(Vat='Z','Zero')) as Vat

FROM

\\appserver1\Qlikview\qlikviewdatafiles\qvds\Products.qvd (qvd) ;

Below is a sample code and output I tried:

Data:
Load
ID,
Type,
Vat as Old_Vat,
If(Vat='S','Standard',
If(Vat='Z','Zero')) as Vat;
Load * Inline [
ID,Type,Vat
10,A,S
20,B,Z
30,C,S
]

Re: ApplyMap

dmxmikey — Tue, 12 Sep 2017 08:47:12 GMT

have used below and it works but was wondering how to add a third condition eg if Vat='H','Reduced?

Products:

Load [product],

If(Vat='S','Standard',
If(Vat='Z','Zero')) as Vat

Re: ApplyMap

micheledenardi — Tue, 12 Sep 2017 08:51:53 GMT

Products:

Load [product],

If(Vat='S','Standard',
If(Vat='Z','Zero',

if(Vat='H','Reduced'))) as Vat

FROM

\\appserver1\Qlikview\qlikviewdatafiles\qvds\Products.qvd (qvd) ;

Re: ApplyMap

Miguel_Angel_Baeyens — Tue, 12 Sep 2017 08:59:08 GMT

It seems that using a mapping load is indeed the right use for what you are looking for.

First you need to define the original and descriptive values, using any form of LOAD, I'm using INLINE for the sake of the example, but it could be from another table, SQL or file:

VatcodeMap:
MAPPING LOAD
  *
INLINE [
Original, Extended
Z, Zero
V, Variable
S, Standard
H, Reduced
];

Then use the ApplyMap() function in the Product table:

Products:
LOAD
  *,
  ApplyMap('VatcodeMap', Vatcode) AS Vatcode_Descriptive
FROM Products.qvd (qvd);