Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
trishita
Contributor III

Problem with mapping load and apply map

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

14 Replies
Highlighted

Re: Problem with mapping load and apply map

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

Highlighted
trishita
Contributor III

Re: Problem with mapping load and apply map

What about the applymap section,

is this syntax right?

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

Highlighted

Re: Problem with mapping load and apply map

What about it? What is the problem there?

Highlighted
trishita
Contributor III

Re: Problem with mapping load and apply map

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

Highlighted

Re: Problem with mapping load and apply map

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]

Highlighted
trishita
Contributor III

Re: Problem with mapping load and apply map

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

Highlighted

Re: Problem with mapping load and apply map

This looks right to be me with SURVEYID

Capture.PNG

Highlighted
trishita
Contributor III

Re: Problem with mapping load and apply map

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 table30.05.png

Highlighted

Re: Problem with mapping load and apply map

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