topic Help with whatif scenarios in QlikView

Help with whatif scenarios

qlikmpate0 — Mon, 13 Nov 2017 13:05:30 GMT

I have a table containing SKU and quantities for a PO.

I want to create different scenario so the correct quantity figure is used.

Scenarios:-

Where the SKU appears once I want to the Quantity to show.

If the ITEM is repeated and committed quantity is null then I want to the Quantity only for the record where there is a value for quantity and committed quantity too. example SKUs where count=2 or can you use where record count is more than 1?

The column 'Figure to use' is the column I need to use. but I need to derive this. How do I do this? Can i compute this in script of use Set analysis in expressions?

I have attached an example. thanks in advance

Re: Help with whatif scenarios

Anonymous — Mon, 13 Nov 2017 13:23:17 GMT

Maybe something like this:

POLines:

LOAD [PO Number],

if(Count>1 and not isnull(QUANTITY) and not isnull(QUANTITY_COMMITTED),SKU,"") as SKU,

ITEM_DESCRIPTION,

PO_LINE_ID,

QUANTITY_COMMITTED,

QUANTITY,

[Figure to Use]

;

LOAD [PO Number],

SKU,

ITEM_DESCRIPTION,

PO_LINE_ID,

QUANTITY_COMMITTED,

QUANTITY,

Count,

// [Zero and >1],

// Different?,

[Figure to Use]

FROM

[..\..\Purchasing\OtherDataSources\Sum up Adjusted Lines on PO.xlsx]

(ooxml, embedded labels, table is Sheet1);

Re: Help with whatif scenarios

Anonymous — Mon, 13 Nov 2017 13:24:55 GMT

I wasn't able to test but the idea here is to use resident load to manipulate the SKU field to only show if there conditions are true:

if(Count>1 and not isnull(QUANTITY) and not isnull(QUANTITY_COMMITTED),SKU,"")