Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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);
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);
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?
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".
Does this look like what you are trying to get?
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',
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);
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.
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.