Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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

1 Solution

Accepted Solutions
rahulpawarb
Specialist III
Specialist III

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

View solution in original post

3 Replies
rahulpawarb
Specialist III
Specialist III

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

Not applicable
Author

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
Specialist III
Specialist III

Cheers,

Rahul