Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
markgraham123
Specialist
Specialist

filter data in script

Hi All,

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

Here is the sample data:

  

SALES_IDNameStatus
1A0
1A1
1B0
1B1
1C0
1D0
1E0
1F0
1G0
1G1

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!

1 Solution

Accepted Solutions
antoniotiman
Master III
Master III

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

View solution in original post

3 Replies
micheledenardi
Specialist II
Specialist II

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;

Michele De Nardi
If a post helps to resolve your issue, please accept it as a Solution.
antoniotiman
Master III
Master III

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
Specialist
Specialist
Author

This worked!

THanks Antonio!