topic Re: How can I map these two tables using mapping function and apply map with conditional expressionbetween 2 tables in QlikView

How can I map these two tables using mapping function and apply map with conditional expressionbetween 2 tables

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

I need to map two fuel types FIELD from 2 tables, Sounding correction and Summary depending on the sulphur content of the sounding correction table.How can I map these two tables using mapping function and apply map with conditional expression

Sounding Correction.Fuel Type	Sounding Correction.Sulphur Content	Summary.Fuel Type
DMA	<.01	LSDO
DMB	<.01	LSDO
DMC	<.01	LSDO
DMA	>.01	HSDO
DMB	>.01	HSDO
DMC	>.01	HSDO
RMA	<.01	ULSFO
RMG	<.01	ULSFO
RMD	<.01	ULSFO
RMA	>.01	HSFO
RMG	>.01	HSFO
RMD	>.01	HSFO

If (Sounding Correction.Fuel Type = ‘DM*’) and (Sounding Correction.Sulphur Content)

Greater than 0.1 , Summary.Fuel Type = ‘HSDO’

Or else ‘LSDO’

If (Sounding Correction.Fuel Type = ‘RM*****’) and (Sounding Correction.Sulphur Content)

Greater than 0.1 then (Summary.Fuel Type) = ‘HSFO’

Or else ‘ULSFO’

Re: How can I map these two tables using mapping function and apply map with conditional expressionbetween 2 tables

olivierrobin — Thu, 24 May 2018 09:14:28 GMT

hello

something like

if(wildmatch([Sounding Correction.Fuel Type ],' ‘DM*’) and [(Sounding Correction.Sulphur Content) ],'HSDO','LSDO') as .....

same logic for the 2nd expression

Re: How can I map these two tables using mapping function and apply map with conditional expressionbetween 2 tables

trishita — Thu, 24 May 2018 09:18:08 GMT

But I want to do it with mapping function, is it possible? secondly HSDO and LSDO will come from the other table, not just any string expression...

my main goal is to map these two tables which have different codes for Fuel type

Re: How can I map these two tables using mapping function and apply map with conditional expressionbetween 2 tables

olivierrobin — Thu, 24 May 2018 09:22:11 GMT

you can code a similar logic tu use applymap

eg:

if(some condition,applymp(code here parameters for yes cond), applymap(code here parameters for no cond)

Re: How can I map these two tables using mapping function and apply map with conditional expressionbetween 2 tables

Anonymous — Thu, 24 May 2018 09:24:05 GMT

Hi Trishita,

For apply map function is Applymap(tablename, mapping field, nofield)

Thanks Regards,

Ramesh.P

Re: How can I map these two tables using mapping function and apply map with conditional expressionbetween 2 tables

shiveshsingh — Thu, 24 May 2018 09:28:32 GMT

Use this as your expression

if(WildMatch([Sounding Correction.Fuel Type], 'DM*') and [Sounding Correction.Sulphur Content] = '>.01','HSDO',

if(WildMatch([Sounding Correction.Fuel Type], 'DM*') and [Sounding Correction.Sulphur Content] <> '>.01','LSDO',

if(WildMatch([Sounding Correction.Fuel Type], 'RM*') and [Sounding Correction.Sulphur Content] = '>.01','HSFO',

if(WildMatch([Sounding Correction.Fuel Type], 'RM*') and [Sounding Correction.Sulphur Content] <> '>.01','ULSFO'))))

Re: How can I map these two tables using mapping function and apply map with conditional expressionbetween 2 tables

trishita — Thu, 24 May 2018 09:31:21 GMT

so I don't need to use mapping load or applymap for this?

Re: How can I map these two tables using mapping function and apply map with conditional expressionbetween 2 tables

olivierrobin — Thu, 24 May 2018 09:33:08 GMT

of course, you must first

use map load to load your mapping tables

Re: How can I map these two tables using mapping function and apply map with conditional expressionbetween 2 tables

trishita — Thu, 24 May 2018 09:33:25 GMT

Hi Oliver,

Can you show me any example like this? I could not find it in any qlikview help or such

Re: How can I map these two tables using mapping function and apply map with conditional expressionbetween 2 tables

shiveshsingh — Thu, 24 May 2018 09:35:12 GMT

Why do you want to use mapping table? Do you want these calculations in script?

I have shared the expression for Straight table.

If you want to use it using mapping table, then it needs to be done in script. Do you have separate mapping table or something?

If you don't have any issues, you can do this in UI layer by using the expression

Re: How can I map these two tables using mapping function and apply map with conditional expressionbetween 2 tables

olivierrobin — Thu, 24 May 2018 09:37:04 GMT

map1:

mapping load

key,value

from ......

where key is the value you are looking for

and value will be the returned value

have a look in QV documention at

8 Introduction to loading data

Using a mapping table

Re: How can I map these two tables using mapping function and apply map with conditional expressionbetween 2 tables

trishita — Thu, 24 May 2018 09:41:10 GMT

because I am trying to connect two tables..these tables have been joined already previously but I was getting redundant data...

Re: How can I map these two tables using mapping function and apply map with conditional expressionbetween 2 tables

shiveshsingh — Thu, 24 May 2018 09:58:09 GMT

So why not using the expression, you'll get the desired result.

Please try it once

Re: How can I map these two tables using mapping function and apply map with conditional expressionbetween 2 tables

trishita — Thu, 24 May 2018 11:22:11 GMT

I am not getting the expression even if I am using it because I want to see values against rmk 700 which is HSFO but its not reflected..if I can map rmk with the exact code then I think the redundant data can be avoided:

if(WildMatch(FUEL_TYPE, 'DM*') and [SULPHUR_CONTENT] = '.1','HSDO',

if(WildMatch(FUEL_TYPE, 'DM*') and [SULPHUR_CONTENT] <> '>.1','LSDO',

if(WildMatch(FUEL_TYPE,'RM****') and [SULPHUR_CONTENT] = '>.1', 'HSFO','ULSFO')))

Result is erroneous: You can see only the highlighted row is the required data..but I have several rows of redundant data...and also it is not ULSFO

Re: How can I map these two tables using mapping function and apply map with conditional expressionbetween 2 tables

shiveshsingh — Thu, 24 May 2018 11:25:37 GMT

ok, so if it's RMK 700 , then you need HSFO in resultant column?

Re: How can I map these two tables using mapping function and apply map with conditional expressionbetween 2 tables

trishita — Thu, 24 May 2018 11:48:51 GMT

RMK 700 if has sulphur content less than .1 will be ULSFO or HSFO.But as the the two columns are not mapped properly u can see there are redundant data...all the columns are showing duplicate data...which is not correct...only rmk 700 which has hsfo has the correct data which is -42.

u can see the other fields are showing redundan data, apart from this highlighted field no other values should come

Re: How can I map these two tables using mapping function and apply map with conditional expressionbetween 2 tables

shiveshsingh — Fri, 25 May 2018 11:49:45 GMT

What are your dimensions?