Grouping multiple values in a column into a single column

Hi,

 

I have the following dataset:

WhseLoc

 Proj Inventory

Previous Inventory

Country

California

18

20

USA

Atlanta

21

22

USA

Columbus

19

20

USA

Toronto

 0

25

Canada

Ottawa

0

20

Canada

Vancouver

15

30

Canada

 

I want to get an output dataset as follows:

Country

Is_out_of_stock

USA

No

Canada

Yes

 

The conditions are: Proj Inventory <20 and Previous Inventory >= 20 for all WhseLocation in the same country.

 

Can someone please help me out.

Thanks in advance.

Comments

  • If you're looking to try a beast mode at the card level, instead of a SQL or Magic ETL dataflow to run the numbers, you'll have a bit of nested code.  Something like this, which may or may not actually work:

    CASE
    WHEN
    COUNT(`WhseLoc`)
    = SUM(CASE WHEN `Proj Inventory` < 20 AND `Previous Inventory` >= 20 THEN 1 ELSE 0 END)
    THEN 'Yes'
    ELSE 'No'
    END