topic Lookup in Qlikview in QlikView

Lookup in Qlikview

miranrai — Mon, 14 Sep 2015 10:18:42 GMT

Hi,

I have a master table which has counter party names (Ex. Citi Corp) and an another table which has list of all counter parties.

Ex:

Master:

Company_Code, TaxCode, Counterparty, Net

Company Code	TAX Code	Counter party	NET
0001	I0	Citi Corp	100
0002	V1	ABC Corp	200
0003	A0	XYZ pvt ltd	300

LookUp:

Counter Party
XYZ pvt ltd
Citi Corp

I want to add a flag to master table script when counter party is available in lookup table then 'Y' else 'N'. I was thinking of using Applymap but as there is no numeric column in lookup table so that possibility ruled out. Any help on this? i want to set up this flag logic on script level.

Re: Lookup in Qlikview

Gysbert_Wassenaar — Mon, 14 Sep 2015 10:22:51 GMT

LookUp:

LOAD [Counter Party], [Counter Party] as CP_LookUp FROM ...

Master:

LOAD Company_Code, TaxCode, [Counter Party], Net, If(Exists(CP_LookUp, [Counter Party],'Y','N') as Flag

From ...

Drop Field CP_LookUp;

Re: Lookup in Qlikview

sunny_talwar — Mon, 14 Sep 2015 10:25:02 GMT

I think you should be able to use a mapping table load here:

Mapping:

Mapping

LOAD * Inline [

Counter Party, Flag

XYZ pvt ltd, 1

Citi Corp, 1

];

MasterTable:

LOAD *,

ApplyMap('Mapping', [Counter party], 0) as Flag;

LOAD * Inline [

Company Code, TAX Code, Counter party, NET

0001, I0, Citi Corp, 100

0002, V1, ABC Corp, 200

0003, A0, XYZ pvt ltd, 300

];

Re: Lookup in Qlikview

Anonymous — Thu, 07 Apr 2016 03:00:43 GMT

Hi Sunny,

I have a similar question as above so thought I would tag it onto this one.

I have loaded Postcodes and State into table "PostCodeMapping"

PostCodeMapping table:-

Postcode State

200 ACT

221 ACT

801 NT

851 NT

1023 NSW

1030 NSW

I now need to add the State the to resident UserTable. I have the postcode in the resident table to use in the lookup. how do i return postcode into the resident table?

Cheers.

Re: Lookup in Qlikview

sunny_talwar — Thu, 07 Apr 2016 09:46:13 GMT

I am assuming that you already have postcode in your usertable and trying to bring State in? May be something like this:

UserTable:

LOAD allYourFields,

postcode,

ApplyMap('PostCodeMapping', postcode, Null()) as State

Resident ...

Re: Lookup in Qlikview

Anonymous — Sat, 09 Apr 2016 01:58:07 GMT

Hi Sunny,

I cant seem to get “Values on data points” to work for multi-lines like the following:-

Is this not possible in Qlik?

Laura Castagna

Business Systems and Process Analyst, NSW Business Chamber

140 Arthur Street North Sydney NSW 2060

Tel: 02 9458 7804 | Mob: 0429 486 934 | Web: www.nswbusinesschamber.com.au<http://www.nswbusinesschamber.com.au>

Twitter<http://www.twitter.com/nswbc> | Facebook<https://www.facebook.com/NSWBusinessChamber> | LinkedIn<http://www.linkedin.com/company/388425?trk=saber_s000001e_1000> | YouTube<http://www.youtube.com/nswbctv>

<http://www.nswbusinesschamber.com.au/>