topic Re: Lookup and Concat in QlikView

Lookup and Concat

Fri, 06 Jul 2012 14:48:52 GMT

I am trying to do a lookup, the problem I have is that there can be several rows with the same ID Value in the lookup table. To get round this I am trying to create a concat field which gives me unique row I am trying to find.

The problem is that I can seem to get the lookup to use the concat field see example below:

[LookupTablet]:

LOAD WaitlistEntryId_Value,
     ListDescription as Description,
     Id_Value,
     Id_Value & ' - ' & ListDescription as IDKey,
     Date1_Value,
     Date2_Value

FROM
........Table1.csv
(txt, unicode, embedded labels, delimiter is ',', msq);

[Patients1]:
LOAD Id_Value,
     Id_Value & ' - ' & 'List1' as IDKey,

    if (lookup('Description', 'IDKey',IDKey,'WaitingList') = 'First Assessment New Referrals', 'TRue','False') as Result1

FROM
..........Table2.csv
(txt, unicode, embedded labels, delimiter is ',', msq);

Any ideas would be great.

Cheers

Simon

Re: Lookup and Concat

jonathandienst — Fri, 06 Jul 2012 14:54:54 GMT

Simon

The problem is that IDKey does not exist in the table source for the second load (you can only refer to fields that exist in the LOAD source). The solution is to use a multilevel load.

This should do the trick:

[Patients1]:

LOAD *,

if (lookup('Description', 'IDKey',IDKey,'WaitingList') = 'First Assessment New Referrals', 'TRue','False') as Result1

;

LOAD Id_Value,

Id_Value & ' - ' & 'List1' as IDKey,

FROM

..........Table2.csv

(txt, unicode, embedded labels, delimiter is ',', msq);

The first LOAD uses the second as a data source, so IDKey exists in the source for the first load...

Hope that helps

Jonathan

Re: Lookup and Concat

Mon, 09 Jul 2012 11:11:00 GMT

Thanks for the reply and it kind of worked. I'll go back to the begining and i'll try and explained what I am try to do and mabe you can help.

I have clirnts who are many lists. The list have a start date and and End date. I want to have a table in QlikView that will show me the client and the start date from onle list and end date from a different list.

Heres an example of the table:

client ID List Name Start Date End Date

1 List1 01/01/12 30/01/12

2 List1 05/01/12 18/01/12

1 List2 30/01/12 30/02/12

2 List2 18/01/12 02/03/12

1 List3 30/02/12 15/04/12

2 List3 02/03/12 18/03/12

So I basically want a table to show me the clients that have a start date of List2 and and End date of List 3:

client ID Start Date End Date

1 30/01/12 15/04/12

2 18/01/12 18/03/12

Is ther an easy way to do this, baring in mind that there are other lists that I'm not bothered about and that I want to do calculations on the dates afterewards in the table.

Thank you in advance, hope this makes sense.

Cheers

Simon

Re: Lookup and Concat

jonathandienst — Tue, 10 Jul 2012 06:33:05 GMT

Simon

I take it that you mean the earlist start date and the latest end date for each client regardless of list?

This snippet should do that:

LOAD clientID,

Min([Start Date]) As [Early Start],

Max([End Date]) As [Late End]

Resident clientList

Group by clientID;

Then use thos fields in a straight table.

Regards

Jonathan

Re: Lookup and Concat

Tue, 10 Jul 2012 08:52:19 GMT

Hi,

I do need to check the list name as there are other lists that could have an earlier date, which I am not interested in.

Would this work:

LOAD clientID,

IF (List='List2',Min([Start Date]) ,'') As [Early Start],

IF (List='List3',Max([End Date]),'') As [Late End]

Resident clientList

Group by clientID;

Cheers

Simon

Re: Lookup and Concat

jonathandienst — Tue, 10 Jul 2012 14:05:25 GMT

Simon

I don't think that will work (but give it a try!), but if not, try this:

DateRange:

LOAD clientID,

Min([Start Date]) As [Early Start]

Resident clientList

Where List = 'List2'

Group by clientID;

Join (DateRange)

LOAD clientID,

Max([End Date]) As [Late End]

Resident clientList

Where List = 'List3'

Group by clientID;

Regards

Jonathan