Using Group By in Qlik Data Load

[Evan0211]

I am working with a data load and I have created a data object that appends a color code based on criteria in the field:

"Package Delivery Status":
Load 
if(not isnull([Completed Date]), 'Reg', //Delivered
    if(not isnull([Delayed Date]) and Today() > [Delayed Date], 'Yellow', //Delayed
    if(not isnull([Delivery Date]) and Today() > [Delivery Date], 'Yellow', //Not delivered and not marked delayed
'Reg'))) as "Status_Color",
"ID" as "Package_ID"
.......

There could be one item or n number of items with the same package ID.

Example

Package_ID	Status_Color
0001	Reg
0001	Yellow
0001	Reg
0002	Reg
0003	Yellow
0003	Reg
0004	Reg
0004	Reg
0004	Reg
0005	Yellow

What I need to do is group all of the items by Package_ID and if ANY of the items in that ID are delayed or not delivered and not marked delayed, set a flag.

In the example above 0001, 0003, and 0005 would have the flag 'Yellow' because they contain at least one Status_Color as Yellow. 0002 and 0004 would be 'Reg' because they do not contain at least one Status_Color as Yellow.

I will then use that flag to change the color of the Package_ID to show that there is an issue within that package.

Any help would be greatly appreciated

[Or · Accepted solution]

Left Join(MainTable)

Load PackageID, 'Yellow' as ActualColor

Resident MainTable

Where Color = 'Yellow';

That will set Yellow for anything that needs it, and null for everything else. If you specifically need the "Reg" for the other values, you can use a mapping load rather than this left join version - load the list of orders that require Yellow, and then use ApplyMap() with its Default Option parameter to fill in anything that didn't have Yellow listed.

If you have multiple colors, I'd suggest you consider using a Dual() type and assign a numeric value to each color so you can use min/max to pick out the most significant color (e.g. 0 is regular, 1 is yellow, 2 is orange, 3 is red, and you pick out the max value for each order).