topic Re: Mapping table with several confitions if the result isnt available in the original table in QlikView

Mapping table with several confitions if the result isnt available in the original table

Mon, 30 Jan 2017 22:39:36 GMT

I have been working with several tables in qlikview however i want to pull some information from one table where i could find the information, however in the case it isnt I want to generate the information using several conditionals using the information that I have in the table where i want to aggregate the information

my script look like this, however it doesnt do what i have to change to make it work

BASE:

LOAD CODIGO,

ORIGEN

FROM

(ooxml, embedded labels, header is 3 lines, table is H_5857);

TEMP_REGALIAS:

MAPPING LOAD CODIGO,

[% REG]/100 as REG

FROM

(ooxml, embedded labels, header is 2 lines, table is [% Regalia]);

TEMP_PREFIJO:

MAPPING LOAD CODIGO,

LEFT(CODIGO,3) AS PREFIJO

FROM

(ooxml, embedded labels, header is 3 lines, table is H_5857);

Royalty:

LOAD

ApplyMap('REG',CODIGO,

IF(NOT MATCH(PREFIJO,'844') and Match([ORIGEN], '2','3','6'),.1,

IF(MATCH(PREFIJO,'844'),.2,

IF(Match([ORIGEN], '1'),0,.15)))) as REGALIA

thanks for your help

regards

Re: Mapping table with several confitions if the result isnt available in the original table

rahulpawarb — Tue, 31 Jan 2017 06:03:01 GMT

Hello Pedro,

Based on my primary analysis I have drafted solution as below. You can provide more clarity on the issue description and expected result. This will help us to provide you more correct inputs.

// You can use ApplyMap as below

//Syntax: ApplyMap('<MappingTable>',<MappingColumn>[,DefaultValue]) as Alias

ApplyMap('TEMP_REGALIAS',CODIGO, IF(LEFT(CODIGO,3) <> '844' AND Match([ORIGEN], '2','3','6'),.1, IF(LEFT(CODIGO,3) ='844',.2, IF(Match([ORIGEN], '1'),0,.15)))) as REGALIA

Hope this will be helpful.

Regards!

Rahul

Re: Mapping table with several confitions if the result isnt available in the original table

Sat, 04 Feb 2017 00:11:08 GMT

Hi Rahul

i just change a little bit mi script using your recommendation and now its working perfectly,.,.,Thanks a lot. for your help

TEMP_REGALIAS:

MAPPING LOAD CODIGO,

[% REG]/100 as REG

FROM

(ooxml, embedded labels, header is 2 lines, table is [% Regalia]);

BASE:

LOAD CODIGO,

ORIGEN,

ApplyMap('TEMP_REGALIAS',CODIGO,

IF(left(CODIGO,3)<>'844' and Match([ORIGEN], '2','3','6'),.1,

IF(left(CODIGO,3)='844',.2,

IF(Match([ORIGEN], '1'),0,.15))))*1 as REGALIA

FROM

(ooxml, embedded labels, header is 3 lines, table is H_5857);

Re: Mapping table with several confitions if the result isnt available in the original table

rahulpawarb — Sun, 05 Feb 2017 16:18:31 GMT

Cheers,

Rahul