topic Re: Field intersection (Load) in QlikView

Field intersection (Load)

carg1 — Sat, 16 Nov 2024 20:21:37 GMT

Dear Qlikview experts,

I have one problem with cleaning my data. I want to import (Load) from table data2 only the data if field Part exist in table data. In another words i don't want to have Parts G,H,I,J,K,L from data2 nether its date and demand loaded. I want to have only parts and its corresponding date and demand if it intersect with Field Part from table data.

data:
Load*Inline [
Date,Part,Quantity on stock
7/13/2019,A,10
7/14/2019,A,20
7/14/2019,B,5
7/14/2019,C,14
7/14/2019,D,18
7/14/2019,E,16
7/14/2019,F,18
7/24/2019,A,25
7/24/2019,B,18
7/24/2019,C,17
7/24/2019,D,13
7/24/2019,E,28
7/24/2019,F,11
];
//Loading demands in future
data2:
Load*Inline [
Date,Part,Demand
7/28/2019,A,5
7/28/2019,B,2
7/28/2019,C,14
7/28/2019,D,10
7/28/2019,E,2
7/28/2019,F,2
7/30/2019,A,5
7/30/2019,B,5
7/30/2019,C,8
7/30/2019,D,5
7/30/2019,E,11
7/30/2019,F,5
7/31/2019,A,3
7/31/2019,B,3
7/31/2019,C,3
7/31/2019,D,3
7/31/2019,E,3
7/31/2019,F,3
8/2/2019,A,5
8/2/2019,B,2
8/2/2019,C,14
8/2/2019,D,10
8/2/2019,E,2
8/2/2019,F,2
8/2/2019,G,5
8/2/2019,H,2
8/2/2019,I,14
8/2/2019,J,10
8/2/2019,K,2
8/2/2019,L,2
];

Possible result would be:

Table:

Load

Date,
Part[Only common one],
[Quantity on stock],
Demand ;

Re: Field intersection (Load)

miskinmaz — Tue, 30 Jul 2019 06:44:34 GMT

You just need add extra line like below while loading your second table like:

Load

....,

Part,

...

from data

Load

....,

Part,

.....

from data2 where exist(Part,Part)

Re: Field intersection (Load)

carg1 — Tue, 30 Jul 2019 06:51:31 GMT

Thanks for Your reply.

In my exact case where should I implement where exist?

//Loading data (from past)
data:
Load*Inline [
Date,Part,Quantity on stock
7/13/2019,A,10
7/14/2019,A,20
7/14/2019,B,5
7/14/2019,C,14
7/14/2019,D,18
7/14/2019,E,16
7/14/2019,F,18
7/24/2019,A,25
7/24/2019,B,18
7/24/2019,C,17
7/24/2019,D,13
7/24/2019,E,28
7/24/2019,F,11
];
//Loading demands in future
data2:
Load*Inline [
Date,Part,Demand
7/28/2019,A,5
7/28/2019,B,2
7/28/2019,C,14
7/28/2019,D,10
7/28/2019,E,2
7/28/2019,F,2
7/30/2019,A,5
7/30/2019,B,5
7/30/2019,C,8
7/30/2019,D,5
7/30/2019,E,11
7/30/2019,F,5
7/31/2019,A,3
7/31/2019,B,3
7/31/2019,C,3
7/31/2019,D,3
7/31/2019,E,3
7/31/2019,F,3
8/2/2019,A,5
8/2/2019,B,2
8/2/2019,C,14
8/2/2019,D,10
8/2/2019,E,2
8/2/2019,F,2
8/2/2019,G,5
8/2/2019,H,2
8/2/2019,I,14
8/2/2019,J,10
8/2/2019,K,2
8/2/2019,L,2
];
//Makeing one table for calculations
Table:
NoConcatenate
LOAD
Date,
Part,
[Quantity on stock]

Resident data;

Concatenate (Table)
LOAD
Date,
Part,
Demand

Resident data2;

Drop Table data;
DROP Table data2;
//Final Table with calculated values
FinalTable:
Load
Date,
Part,
[Quantity on stock],
Demand,
If(IsNull([Quantity on stock]),Peek(Forecast,-1),[Quantity on stock]) as Forecast,
If(IsNull(Demand),0, Peek(ACCDemand,-1)+ Demand) as ACCDemand

Resident Table order by Part;
DROP Table Table;

Re: Field intersection (Load)

miskinmaz — Tue, 30 Jul 2019 06:58:08 GMT

While loading your second table for first time.

Re: Field intersection (Load)

carg1 — Tue, 30 Jul 2019 07:14:10 GMT

Thank you very much.