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

[#1]

Hi

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

[rahulpawarb · Accepted solution]

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

[#3]

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

[rahulpawarb]

Cheers,

Rahul