please Help me with how to use Applymap() instead of left join

[#1]

Table1:

LOAD

   ows_state as State,

    ows_Country as Country,

FROM table.xml;

Left Join(Tab1)

LOAD ISO_2DIGIT as CountryAbbrev,

  Name as Country

  FROM

  [..\data\World Country Reference.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Left Join(Tab1)

LOAD name as StateName,

  id as State

  From

[..\data\US States Reference.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

[sunny_talwar · Accepted solution]

May be this:

MappingCountry:

Mapping

LOAD Name,

           ISO_2DIGIT

FROM

[..\data\World Country Reference.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

MappingState:

Mapping

LOAD id,

          name

From

[..\data\US States Reference.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Table1:

LOAD *,

          ApplyMap('MappingCountry', Country) as CountryAbbrev,

          ApplyMap('MappingState', State) as StateName;

LOAD ows_state as State,

          ows_Country as Country

FROM table.xml;

[Anonymous]

I'm agree with sunindia but you've lose the semicolon in the precent load

[#4]

ApplyMap we use when we have to assign some default value when Left Table is not having corresponding record.

For Example: You have a table T1 which is having country code and you have another table T2 which is having Country code Description. But T2 is not having all the Country code description available in T1 and in this case you want to apply default county as Australia.

T1

County Code C1 C2

12345

T2

Country Code  County Dscription

12344             India

12346             China

Now write a query to get Country code, C1, C2 as well as Description and you will see that for country code 12345 there is no value in T2 and In this case you want to assign a value Australia then you have to use Applymap.

[MayilVahanan]

Hi

Try like this, Slight modification in SunIndia script part

Table1:

LOAD *,

          ApplyMap('MappingCountry', Country , 'N/A') as CountryAbbrev,

          ApplyMap('MappingState', State, 'N/A') as StateName;

LOAD ows_state as State,

          ows_Country as Country

FROM table.xml;

You can easily identify the values which are not mapped with country and state by selecting N/A.

[qlikviewwizard]

MayilVahanan

Good explanation.

[MayilVahanan]

Thank you

[qlikviewwizard]

Hi sshettys

If you get the answer, please select Correct Answer. Thank you.

[#9]

Table1:

LOAD *,

          ApplyMap('MappingCountry', Country) as CountryAbbrev,

          ApplyMap('MappingState', State) as StateName;

LOAD ows_state as State,

          ows_Country as Country

FROM table.xml;

MappingCountry:

Mapping

LOAD Name,

     ISO_2DIGIT

FROM

[..\data\World Country Reference.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

MappingState:

Mapping

LOAD id,

name

From

[..\data\US States Reference.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

[sasiparupudi1]

Please close this thread if you got your answer or share some sample data so that we can provide you with a solution