Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
miranrai
Contributor III
Contributor III

Lookup in Qlikview

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 CodeTAX CodeCounter partyNET
0001I0Citi Corp100
0002V1ABC Corp200
0003A0XYZ pvt ltd300

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.

1 Solution

Accepted Solutions
Gysbert_Wassenaar

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;


talk is cheap, supply exceeds demand

View solution in original post

5 Replies
Gysbert_Wassenaar

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;


talk is cheap, supply exceeds demand
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
Not applicable

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
Not applicable

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