topic Re: Re: Intervalmatch help needed in QlikView

Intervalmatch help needed

Tue, 28 Oct 2014 08:08:02 GMT

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:

I have attached the qvd file for this simplified example.

Re: Intervalmatch help needed

Gysbert_Wassenaar — Tue, 28 Oct 2014 08:28:45 GMT

See attached qvw.

Re: Intervalmatch help needed

Tue, 28 Oct 2014 08:44:25 GMT

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

Gysbert_Wassenaar — Tue, 28 Oct 2014 08:48:29 GMT

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

Re: Intervalmatch help needed

Tue, 28 Oct 2014 08:58:14 GMT

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

Gysbert_Wassenaar — Tue, 28 Oct 2014 09:05:21 GMT

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.

Re: Intervalmatch help needed

Tue, 28 Oct 2014 09:07:47 GMT

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

Gysbert_Wassenaar — Tue, 28 Oct 2014 15:03:01 GMT

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;