Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Intervalmatch help needed

Hi,

I need to combine two tables into one to analyze actual demand vs shipped qty but I have not found a good way to do this yet.

Hope somebody could help me with this.

The challenge is that table for actual demand is aggregated by date (timestamp) while shipped qty is aggregated by period with a from - to timestamp.

This shows the intended result:

Demand_ShipptedQty.JPG.jpg

I have attached the qvd file for this simplified example.

7 Replies

Re: Intervalmatch help needed

See attached qvw.


talk is cheap, supply exceeds demand
Not applicable

Re: Intervalmatch help needed

Thanks Gysbert,

The actual data contains many million rows. Do you recommend this solution in this case, or is there a different method to sum(ActualDemandQty) in the script?

Re: Intervalmatch help needed

Surely you don't intend to show all the millions of rows in a sheet object at the lowest level of detail?


talk is cheap, supply exceeds demand
Not applicable

Re: Intervalmatch help needed

The table showing the ship qty also contains other information such as forecast for the period.

So what I really want to do is to analyze Forecast vs Actual Demand vs Shipped Qty over different time intervals.

Actual demand I need to get from a different table and this is showing demand per date, not per period.

So I have about 30.000 items and 150 periods per item.

I need to show this data for every item and then I will have to aggregate periods to show e.g. YTD, LY, Quarters etc. and calculate forecast accuracy over the time interval and highlight items with low accuracy.

Ideally (I think..) I would want the sum(actual demand qty) joined into the shipqty table.

Not sure if the explanation helps?

Re: Intervalmatch help needed

If you want you can join all the data in the script into one table. Or you can create additional summary tables. But I'd start with giving the users options to make selections so they're not confronted with non-performing sheet objects that try to show 30.000 items over 150 periods at the same time.


talk is cheap, supply exceeds demand
Not applicable

Re: Intervalmatch help needed

I agree.

How would you do it if we were to join all data into one table in the example attached?

Regards,
Freddy

Re: Re: Intervalmatch help needed

Not that I recommend it but, this should do it:

ActualDemand:

LOAD WarehouseItem,

    Date_ActualDemand,

    Qty_ActualDemand

FROM [ActualDemand.qvd] (qvd);

Tmp:

LOAD WarehouseItem,

    [Shipped Qty],

    PeriodFromDate,

    PeriodToDate

FROM [ActualDeliveries.qvd] (qvd);

join(ActualDemand)

IntervalMatch(Date_ActualDemand,WarehouseItem)

LOAD PeriodFromDate,PeriodToDate,WarehouseItem

Resident Tmp;

join(ActualDemand)

LOAD * Resident Tmp;

Drop Table Tmp;


talk is cheap, supply exceeds demand
Community Browser