topic Expression Help3 in QlikView

Expression Help3

Anonymous — Tue, 15 Mar 2016 03:19:59 GMT

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

Re: Expression Help3

jagan — Tue, 15 Mar 2016 03:56:23 GMT

HI,

Attach sample data in Excel.

Regards,

Jagan.

Re: Expression Help3

robert_mika — Tue, 15 Mar 2016 04:00:20 GMT

Try ApplyMap

Don't join - use Applymap instead

Re: Expression Help3

Anonymous — Tue, 15 Mar 2016 04:07:59 GMT

Hi Jagan,

See attached.

Re: Expression Help3

jagan — Tue, 15 Mar 2016 04:26:34 GMT

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.

Re: Expression Help3

Anonymous — Tue, 15 Mar 2016 04:35:30 GMT

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

Re: Expression Help3

Anonymous — Tue, 15 Mar 2016 20:30:04 GMT

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

Re: Expression Help3

Anonymous — Wed, 16 Mar 2016 13:13:17 GMT

Jagan,

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