How to use lookup?

[Anonymous]

Hi QV-users.

What I am trying to do is really simple in Excel but in QlikView I can't figure it out.

I the table I have 5 columns with data (OrderNo, Dep, Status, SubNo and Date) and 3 columns with formulas (Key_OrderNo_Date, MainNo and MainStatus).

Row/column	A	B	C	D	E	F	G	H
1	Key_OrderNo_Date	OrderNo	Dep	Status	SubNo	Date	MainNo	MainStatus
2	A12345640182	A123456	H8	1		04-01-2010	A123456	1
3	D45678540182	D456785		KOMP	A123456	04-01-2010	A123456	1
4	B24356440182	B243564	G3	2		04-01-2010	B243564	2
5	C65478540182	C654785	R2	2		04-01-2010	C654785	2
6	B48652440182	B486524	O4	1		04-01-2010	B486524	1
7	A12345640190	A123456	R2	2		12-01-2010	A123456	2
8	D45678540190	D456785		KOMP	A123456	12-01-2010	A123456	2
9	B24356440190	B243564	G3	3		12-01-2010	B243564	3
10	C65478540190	C654785	R2	3		12-01-2010	C654785	3
11	B48652440190	B486524	O4	5		12-01-2010	B486524	5

The OrderNo is the only unique column.

The Excel-formula for Key_OrderNo_Date is : =OrderNo & Date [e.g.: =B2&F2)].

The Excel-formula for MainNo is: =IF(Status="KOMP";SubNo;OrderNo) [e.g.: =IF(D2="KOMP";E2;B2)].

The Excel-formula for MainStatus is: =IF(Status="KOMP";VLOOKUP(SubNo&Date;Key_OrderNo_Date:Status;4;FALSE);Status) [e.g.: =IF(D2="KOMP";VLOOKUP(E2&F2;A2:D2;4;FALSE);D2)].

The MainStatus column is the problem. If the OrderNo has the status: KOMP, there will be no Dep but always a SubNo refering to one of the other OrderNo. Then my Excel-formula in MainStatus make a vlookup on the SubNo & Date to find it in Key_OrderNo_Date and return the Status for this.

E.g.: The OrderNo D456785 on the 04-01-2010 has the Status value KOMP and the SubNo A123456. Then it returns the Status value 1 for the OrderNo A123456 in the MainStatus column. On the 12-01-2010 the Status has changed to the value 2. If I don't make the Key_OrderNo_Date, the Status value returned will always be 1 because it looksup for the first OrderNo.

I really hope that some of you out there can help me solve this problem in QlikView.

Best regards

Johnni Kirstein

[Anonymous · Accepted solution]

Hi, Johnni,

Use of QlikView's lookup function is quite simple if you follow example, given in help

Your script will look similar to...

Normal 0 false false false LV X-NONE X-NONE MicrosoftInternetExplorer4 Normal 0 false false false LV X-NONE X-NONE MicrosoftInternetExplorer4

t1:
LOAD * INLINE [
OrderNo, Dep, Status, SubNo, Date
A123456, H8, 1, , 40182
D456785, , KOMP, A123456, 40182
B243564, G3, 2, , 40182
C654785, R2, 2, , 40182
B486524, O4, 1, , 40182
A123456, R2, 2, , 40190
D456785, , KOMP, A123456, 40190
B243564, G3, 3, , 40190
C654785, R2, 3, , 40190
B486524, O4, 5, , 40190
];

t2:
Load OrderNo & Date as Key_OrderNo_Date
,OrderNo
,Dep
,Status
,SubNo
,Date
,IF(Status='KOMP',SubNo,OrderNo) as MainNo
Resident t1;
Drop Table t1;

t3:
Load *
,IF(Status='KOMP',LOOKUP(Status,Key_OrderNo_Date,SubNo&Date,'t2'),Status) as MainStatus
Resident t2;
Drop Table t2;

[#3]

Hi,

You could use a simple lookup during load of the table. It would look something similar. You will have to replace the Load inline with whatever datasource you are using.

Tab1:
Load OrderNo,Dep,Status,SubNo,Date,OrderNo&num(Date) as Key_OrderNo_Date,if(Status='KOMP',Lookup('Status','Key_OrderNo_Date',SubNo&num(Date)),Status) as MainStatus;
LOAD * INLINE [
 OrderNo, Dep, Status, SubNo, Date
 A123456, H8, 1, , 04/01/2010
 D456785, , KOMP, A123456, 04/01/2010
 B243564, G3, 2, , 04/01/2010
 C654785, R2, 2, , 04/01/2010
 B486524, O4, 1, , 04/01/2010
 A123456, R2, 2, , 12/01/2010
 D456785, , KOMP, A123456, 12/01/2010
 B243564, G3, 3, , 12/01/2010
 C654785, R2, 3, , 12/01/2010
 B486524, O4, 5, , 12/01/2010
];