Lookup and Concat

[#1]

Hi

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

[jonathandienst]

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

[#3]

Hi

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

[jonathandienst]

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

[#5]

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

[jonathandienst · Accepted solution]

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