Lookup in Qlikview

[miranrai]

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.

[Gysbert_Wassenaar · Accepted solution]

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;

[sunny_talwar]

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

];

[Anonymous]

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.

[sunny_talwar]

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

[Anonymous]

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/>