filter data in script

[markgraham123]

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!

[micheledenardi]

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;

[antoniotiman · Accepted solution]

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

[markgraham123]

This worked!

THanks Antonio!