topic Re: ApplyMap Help in QlikView

ApplyMap Help

Anonymous — Wed, 16 Mar 2016 17:34:40 GMT

Hi All,

I'm having issues with ApplyMap where few of the "OverallValue" show's "NA". If you look at Sub Company "Kabob II', value should be 170,000 (50% of Company "Kabob II') . Attached Excel file with vlookup and QVW.

Many Thanks,

Frank

Note: Previously Jagan mohan had helped me out on ApplyMap

Re: Expression Help3

Re: ApplyMap Help

sunny_talwar — Wed, 16 Mar 2016 17:56:39 GMT

The problem is that the Cherry doesn't make it to the OverallValueMapping table because of the where statement in Data load where you have IsCompany = 0. If Cherry is not available in the mapping table, you will see NA. Not sure if the Where statement is an important component here, but removing it might fix the issue

This is what is available when I run the below code:

CompanyMap:

MAPPING LOAD DISTINCT

Company,

[Sub Company]

FROM

[test (3).xlsx]

(ooxml, embedded labels, table is Sheet1);

Data:

LOAD

Multiplier * [Total Value] AS OverallValue

WHERE IsCompany = 0;

LOAD Company,

[Sub Company],

Multiplier,

[Total Value],

ApplyMap('CompanyMap', [Sub Company], '0') AS IsCompany

FROM

[test (3).xlsx]

(ooxml, embedded labels, table is Sheet1);

Re: ApplyMap Help

sunny_talwar — Wed, 16 Mar 2016 18:05:16 GMT

I am getting very confused now. Do you want this to be mapped based on Kabob II or the SubCompany where Company is Kabob? Excel is doing it based on Kabob II as SubCompany and QVW is doing it based on Kabob II as the Company and Cherry as Sub Company? Which one is right?

Re: ApplyMap Help

Anonymous — Wed, 16 Mar 2016 18:20:35 GMT

Again Thanks Sunny!!

I want it to mapped at SubCompany.like in Excel. The "Overallvalue" should be the same as Excel column E "Vlookup Amount".

Re: ApplyMap Help

sunny_talwar — Wed, 16 Mar 2016 18:23:09 GMT

Does this look like what you are trying to get?

Re: ApplyMap Help

Anonymous — Wed, 16 Mar 2016 18:40:33 GMT

It's close. SubCompany "Kabob II" OverallValue should equal to 170,000 which is 50% of Company "Kabob II" (340,000), the very last record.

I know you added below line for [Sub Company])) = 'ABC' . What If I have multiple SubCompany without any map to Company? I want it to be more dynamic, can a variable be added?

ApplyMap('OverallValueMapping', If(ApplyMap('CompanySubcompanyMapping', ApplyMap('CompanySubcompanyMapping', [Sub Company])) = 'ABC',

Re: ApplyMap Help

sunny_talwar — Wed, 16 Mar 2016 18:58:08 GMT

The code can def. be fine tuned by using For Loop, but for now it seems to be somewhat working

CompanyMap:

MAPPING LOAD DISTINCT

Company,

[Sub Company]

FROM

[test (3).xlsx]

(ooxml, embedded labels, table is Sheet1);

Data:

LOAD

(Multiplier * [Total Value])/100 AS OverallValue

Where IsCompany = 0;

LOAD Company,

[Sub Company],

Multiplier,

[Total Value],

ApplyMap('CompanyMap', [Sub Company], '0') AS IsCompany

FROM

[test (3).xlsx]

(ooxml, embedded labels, table is Sheet1);

OverallValueMapping:

Mapping

LOAD Company,

OverallValue

Resident Data;

CompanySubcompanyMapping:

Mapping

LOAD DISTINCT Company,

[Sub Company]

FROM

[test (3).xlsx]

(ooxml, embedded labels, table is Sheet1);

OverallValue:

LOAD Company,

OverallValue,

Multiplier

Resident Data;

Concatenate (OverallValue)

LOAD Company,

(ApplyMap('OverallValueMapping', [Sub Company]) * Multiplier)/100 as OverallValue,

Multiplier

Where IsCompany <> 0 and IsNum(ApplyMap('OverallValueMapping', [Sub Company]));

LOAD [Sub Company],

Company,

Multiplier,

ApplyMap('CompanyMap', [Sub Company], '0') AS IsCompany

FROM

[test (3).xlsx]

(ooxml, embedded labels, table is Sheet1);

OverallValueMapping2:

Mapping

LOAD Company,

OverallValue

Resident OverallValue;

Concatenate (OverallValue)

LOAD Company,

(ApplyMap('OverallValueMapping2', [Sub Company]) * Multiplier)/100 as OverallValue,

Multiplier

Where IsCompany <> 0 and IsNum(ApplyMap('OverallValueMapping2', [Sub Company]));

LOAD [Sub Company],

Company,

Multiplier,

ApplyMap('CompanyMap', [Sub Company], '0') AS IsCompany

FROM

[test (3).xlsx]

(ooxml, embedded labels, table is Sheet1);

OverallValueMapping3:

Mapping

LOAD Company,

OverallValue

Resident OverallValue;

Concatenate (OverallValue)

LOAD Company,

(ApplyMap('OverallValueMapping3', [Sub Company]) * Multiplier)/100 as OverallValue,

Multiplier

Where IsCompany <> 0 and IsNum(ApplyMap('OverallValueMapping3', [Sub Company]));

LOAD [Sub Company],

Company,

Multiplier,

ApplyMap('CompanyMap', [Sub Company], '0') AS IsCompany

FROM

[test (3).xlsx]

(ooxml, embedded labels, table is Sheet1);

OverallValueMapping4:

Mapping

LOAD Company,

OverallValue

Resident OverallValue;

DROP Table OverallValue;

Concatenate(Data)

LOAD *,

ApplyMap('OverallValueMapping4', [Sub Company], 'NA') as OverallValue

Where IsCompany <> 0;

LOAD Company,

[Sub Company],

Multiplier,

[Total Value],

ApplyMap('CompanyMap', [Sub Company], '0') AS IsCompany

FROM

[test (3).xlsx]

(ooxml, embedded labels, table is Sheet1);

Re: ApplyMap Help

Anonymous — Wed, 16 Mar 2016 19:42:47 GMT

Many Thanks Sunny! you are awesome!

Do you have any advice how I can improve my scripting? I'm just a beginner or not even with scripts.

Will keep you posted if I heard something.

Re: ApplyMap Help

sunny_talwar — Wed, 16 Mar 2016 19:47:21 GMT

The best way is to keep trying. Try to break the problem into small piece and put each piece of the script together. Once you do that, the logic will start to make a lot more sense. That's what I had to do with the application you just shared, I just couldn't understand what was missing and later I realized that the mapping table needs to have all the Company names to be able to Map. That's what I did. Although, as I mentioned it is not the best code out there and can be improved upon, but it is a good start. May be try spending some time to implement For loop in there to simplify the code.