Grouping multiple values in a column into a single column

Prajju

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.

AS

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