Expression Help3

[Anonymous]

Hi All,

I'm look for a vlookup function in Qlik.. Column E is what I'm look for. Below is what I have in Excel.

=IFERROR(((VLOOKUP(B2,$A$2:$E$13,5,FALSE)*C2)/100),(C2*E2)/100)

A	B	C	D	E
Company	Sub Company	Multiplier	Original Amount	Vlookup Amount
Orange	ABC	5.0000	1,000,000	5,000
Banana	ABC	0.5000	1,000,000	500
Apple	ABC	3.5000	1,000,000	3,500
Pineapple	ABC	1.2500	1,000,000	1,250
XYZ	ABC	85.0000	1,000,000	85,000
Sin	ABC	4.7500	1,000,000	4,750
Alpha	Orange	100.0000	1,000,000	5,000
DEF	Banana	100.0000	1,000,000	500
MNO	Apple	100.0000	1,000,000	3,500
PIE	Pineapple	100.0000	1,000,000	1,250
Cherry	XYZ	100.0000	1,000,000	85,000
Stone	Cherry	100.0000	1,000,000	85,000

Many Thanks,

Frank

[jagan]

HI,

Attach sample data in Excel.

Regards,

Jagan.

[robert_mika]

Try ApplyMap

Don't join - use Applymap instead

[Anonymous]

Hi Jagan,

See attached.

[jagan · Accepted solution]

HI,

Try like this

CompanyMap:

MAPPING LOAD DISTINCT

Company,

[Sub Company]

FROM

[test (2).xlsx]

(ooxml, embedded labels, header is 1 lines, 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 (2).xlsx]

(ooxml, embedded labels, header is 1 lines, table is Sheet1);

OverallValueMapping:

MAPPING LOAD

Company,

OverallValue

RESIDENT Data

WHERE 1 = 1;

CompanySubcompanyMapping:

MAPPING LOAD DISTINCT

Company,

[Sub Company]

FROM

[test (2).xlsx]

(ooxml, embedded labels, header is 1 lines, table is Sheet1);

Concatenate(Data)

LOAD

*,

If(ApplyMap('OverallValueMapping', [Sub Company], 'NA') <> 'NA',  ApplyMap('OverallValueMapping', [Sub Company], 'NA'), 

  ApplyMap('OverallValueMapping', Applymap('CompanySubcompanyMapping', [Sub Company]), 'NA'))AS OverallValue

WHERE IsCompany <> 0;

LOAD Company,

     [Sub Company],

     Multiplier,

     [Total Value],

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

FROM

[test (2).xlsx]

(ooxml, embedded labels, header is 1 lines, table is Sheet1);

Regards,

jagan.

[Anonymous]

Thanks  Jagan. Will test it out later. This is new and advance  for me.

[Anonymous]

Hi Jagan,

I need your help again. Some of the "Overallvalue" are not correct. I'm not sure how two Sub Company ="NA".  If you check Sub Company - Kabob II and Cherry, the 'OverallValue" doesn't seems right.  I have attached my sample Excel and QVW files.

Many Thanks,

Frank

[Anonymous]

Jagan,

Were you able to help? I'm having issues with the "NA" and dupes not showing up.