Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
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;
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
This worked!
THanks Antonio!