topic Re: Problem with mapping load and apply map in QlikView

Problem with mapping load and apply map

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

I have to map two fields from two different tables which are already joined..I want to do it because if i dont map them properly they are showing me redundant data even if the tables are joined.please help me.

The logic is all the fuel type like DMA / DMC can be classifyed as 2 groups HSDO and LSDO on basis of sulphur content.If Fuel type is RMA ... RMG it can be classified as HSFO OR LSFO if sulphur content is more than 0.01 and less than 0.01 rspectively.

.I want to map them because with conditional logic they are still showing redundant data

I want to implement this in my mapping table...Could this syntax be like:

Could this syntax be like:

MAP1:

MAPPING LOAD

SURVEYID,

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

if(WildMatch([Fuel Type], 'DM*') and [Sulphur Content] = ' <:01','LSDO',

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

if(WildMatch([Fuel Type], 'RM*') and [Sulphur Content] = ' <01' ,'ULSFO'))))

as NIFuelType

FROM [mo_bun_sur_survey_view.qvd]

(qvd);

Main:

LOAD

SURVEY_ID,

applymap('MAP1',NIFuelType, 'Unknown') as ActualFueltype

FROM

[mo_purch_sounding_correction_view.qvd]

(qvd);

Re: Problem with mapping load and apply map

sunny_talwar — Wed, 30 May 2018 11:20:25 GMT

May be this

MAP1:

MAPPING

LOAD SURVEYID,

If(WildMatch([Fuel Type], 'DM*'),

If([Sulphur Content] > 0.01, 'HSDO', 'LSDO'),

If(WildMatch([Fuel Type], 'RM*'),

If([Sulphur Content] > 0.01,'HSFO', 'ULSFO'))) as NIFuelType

FROM [mo_bun_sur_survey_view.qvd] (qvd);

Re: Problem with mapping load and apply map

trishita — Wed, 30 May 2018 11:23:40 GMT

What about the applymap section,

is this syntax right?

applymap('MAP1',NIFuelType, 'Unknown') as ActualFueltype

Re: Problem with mapping load and apply map

sunny_talwar — Wed, 30 May 2018 11:29:33 GMT

What about it? What is the problem there?

Re: Problem with mapping load and apply map

trishita — Wed, 30 May 2018 11:39:28 GMT

i DONT KNOW IF ITS RIGHT OR NOT::I mean should i write NIFuelType or the field Fuel Type from the second table where I want to map the previous values

Re: Problem with mapping load and apply map

sunny_talwar — Wed, 30 May 2018 11:48:32 GMT

Do you have a field named NIFuelType in [mo_purch_sounding_correction_view.qvd]? If you do, then you are all good.. if you don't then use the field from [mo_purch_sounding_correction_view.qvd] which matches up with SURVEYID to get NIFuelType from [mo_bun_sur_survey_view.qvd]

Re: Problem with mapping load and apply map

trishita — Wed, 30 May 2018 12:25:32 GMT

NIFuelType is a field i just created in [mo_bun_sur_survey_view.qvd]
for mapping..

mo_bun_sur_survey_view.qv AND [mo_bun_sur_oil_summary_view.qvd] HAVE ONLY ONE FIELD IN COMMON WHICH IS SURVEYID.

So what could me my final code in this regard:

MAP1:

MAPPING

LOAD SURVEYID,

If(WildMatch([Fuel Type], 'DM*'),

If([Sulphur Content] > 0.01, 'HSDO', 'LSDO'),

If(WildMatch([Fuel Type], 'RM*'),

If([Sulphur Content] > 0.01,'HSFO', 'ULSFO'))) as NIFuelType

FROM [mo_bun_sur_survey_view.qvd] (qvd);

FROM [mo_bun_sur_survey_view.qvd]

(qvd);

Main:

LOAD

SURVEY_ID,

applymap('MAP1',SURVEYID, 'Unknown') as ActualFueltype // NIFuelType OR SURVEYID

FROM

[mo_bun_sur_oil_summary_view.qvd]

(qvd);

Re: Problem with mapping load and apply map

sunny_talwar — Wed, 30 May 2018 12:28:35 GMT

This looks right to be me with SURVEYID

Re: Problem with mapping load and apply map

trishita — Wed, 30 May 2018 12:42:26 GMT

But sorry, i just remember that the two tables are not linked with SURVEY_ID.Actually this table has [mo_bun_sur_oil_summary_view.qvd] and [mo_purch_sounding_correction_view.qvd] are indirectly joined as

[mo_purch_sounding_correction_view.qvd] and [mo_bun_sur_survey_view.qvd] are already joined with IntervalMatch function. and the third table (where i want to map ) is directly joined to first table [mo_bun_sur_survey_view.qvd] .

But the fuel type are not here and so I cant directly join with surveyid...

In an nutshell [mo_bun_sur_oil_summary_view.qvd] which has field Fuel Type

and mo_purch_sounding_correction_view.qvd] which has field FUELTYPE and SULPHURCONTENT do not have a direct id...but indirectly added to the main table

Re: Problem with mapping load and apply map

sunny_talwar — Wed, 30 May 2018 14:06:31 GMT

If they are already joined, then why are using mapping load?

Re: Problem with mapping load and apply map

trishita — Thu, 31 May 2018 14:08:40 GMT

Because I am having redundant data as the fuel type of the two tables are not mapped

Re: Problem with mapping load and apply map

sunny_talwar — Thu, 31 May 2018 15:23:38 GMT

Unfortunately, I am not able to understand the problem. Are you able to share a qvw file?

Re: Problem with mapping load and apply map

trishita — Tue, 05 Jun 2018 10:03:57 GMT

PFA

Re: Problem with mapping load and apply map

trishita — Tue, 05 Jun 2018 10:04:33 GMT

Hi Sunny, I have uploaded the qvw

Re: Problem with mapping load and apply map

sunny_talwar — Tue, 05 Jun 2018 11:15:05 GMT

Sorry, it is been few days before I last saw this, would you be able to briefly explain (too lazy to read the whole thing again) what are we trying to do and what from the qvw sample doesn't look right