ApplyMap Help

[Anonymous]

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

[sunny_talwar]

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

[sunny_talwar]

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?

[Anonymous]

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".

[sunny_talwar]

Does this look like what you are trying to get?

[Anonymous]

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',

[sunny_talwar · Accepted solution]

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

[Anonymous]

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.

[sunny_talwar]

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.