topic Re: filter data in script in QlikView

filter data in script

markgraham123 — Tue, 30 May 2017 14:56:35 GMT

Hi All,

I'm trying to separate my data by its status: Please see attached qvw

Here is the sample data:

SALES_ID	Name	Status
1	A	0
1	A	1
1	B	0
1	B	1
1	C	0
1	D	0
1	E	0
1	F	0
1	G	0
1	G	1

2 A 0

2 A 1

2 B 0

2 C 0

For SALES_ID 1,

I want to bring just C,D,E,F in my new column as it doesn't have Status 1

For SALES_ID 2,

I want to bring just B,C in my new column as it doesn't have Status 1

Any help is highly appreciated!

Re: filter data in script

micheledenardi — Tue, 30 May 2017 15:02:36 GMT

Sales1:

Load

SALES_ID as SalesIdSales1,

Name as NameSales1

Status as StatusSales1

from source.xls

where match(Name,'C','D','E','F') and Status<>1;

Sales2:

Load

SALES_ID as SalesIdSales2,

Name as NameSales2

Status as StatusSales2

from source.xls

where match(Name,'B','C') and Status<>1;

Re: filter data in script

antoniotiman — Tue, 30 May 2017 15:17:12 GMT

Hi Marc,

Temp:
LOAD * Inline [
SALES_ID, Name, Status
1, A, 0
1, A, 1
1, B, 0
1, B, 1
1, C, 0
1, D, 0
1, E, 0
1, F, 0
1, G, 0
1, G, 1
2, A, 0
2,A, 1
2,B, 0
2,C, 0
];
Load * Where Flag=0;
Left Join
LOAD SALES_ID,Name,Sum(Status) as Flag
Resident Temp
Group By SALES_ID,Name ;
Drop Table Temp;

Regards,

Antonio

Re: filter data in script

markgraham123 — Tue, 30 May 2017 15:34:17 GMT

This worked!

THanks Antonio!