topic Re: New table with values from another tables in QlikView

New table with values from another tables

bamtor — Sat, 16 Nov 2024 00:27:39 GMT

Hello, community.
I need to create a table with a Forecast sales, the quantity values of which are taken from existing tables - Plan, Order and Shipped.

Plan:

LOAD * INLINE [
Document, Dates, Quantity
Plan, 04.06, 10
Plan, 05.06, 10
Plan, 06.06, 0
];

Order:

LOAD * INLINE [
Document, Dates, Quantity
Order, 04.06, 0
Order, 05.06, 20
Order, 06.06, 20
];

Shipped:

LOAD * INLINE [
Document, Dates, Quantity
Shipped, 04.06, 0
Shipped, 05.06, 0
Shipped, 06.06, 30
];

STORE Main into C:\Temp\test.qvd(qvd);

Drop Table Main;

I tried to do this - concatenate these three tables and based on it create a table with forecast, applying the If-condition to the Quantity .

Forecast:
LOAD
Date(Dates) as Dates,
If (Document = 'Plan' and Quantity > 0, Quantity,
If (Document = 'Order' and Quantity > 0, Quantity,
If (Document = 'Shipped' and Quantity > 0, Quantity
)
)
) as Quantity
FROM
[C:\Temp\test.qvd]
(qvd);

Join (Forecast)
LOAD * INLINE [
Document
Forecast
];

Concatenate
Load
*
FROM
[C:\Temp\test.qvd]
(qvd);

But the values in Forecast take every value from these tables, but i need only one form Plan, if it's null, then Order, if Order is null, then take value from Shipped.

Maybe this task is solved somehow differently?

Re: New table with values from another tables

Vegar — Thu, 18 Jun 2020 10:51:39 GMT

Try this to create your Forecast table:

Forecast:
LOAD
'Forecast' as Document,
Date(Dates) as Dates,
SUM(Quantity) as Quantity
FROM
[C:\Temp\test.qvd] (qvd)
GROUP BY
Dates;

Re: New table with values from another tables

bamtor — Thu, 18 Jun 2020 11:01:47 GMT

Hi, @Vegar!

This option summarizes the values of each table, but it is necessary that the values are not summarized but taken by condition, if the Quantity has a value in the Plan, then take the Quantity value from the Plan, if there is no value in the Plan, then take the value from the Order, if there is no value, then from Shipped.

Re: New table with values from another tables

Vegar — Thu, 18 Jun 2020 11:36:46 GMT

Is this what you are lookin for?

Main: LOAD * INLINE [ Document, Dates, Quantity Plan, 04.06, 10 Plan, 05.06, 10 Plan, 06.06, 0 ] ; LOAD * INLINE [ Document, Dates, Quantity Order, 04.06, 0 Order, 05.06, 20 Order, 06.06, 20 ]; LOAD * INLINE [ Document, Dates, Quantity Shipped, 04.06, 0 Shipped, 05.06, 0 Shipped, 06.06, 30 ]; Concatenate LOAD 'Forecast' as Document , Dates, Dates as TMPDates, Quantity Resident Main Where Document = 'Plan' and Quantity <> 0; Concatenate LOAD 'Forecast' as Document , Dates, Dates as TMPDates, Quantity Resident Main Where Document = 'Order' and Quantity <> 0 AND NOT EXISTS (TMPDates, Dates); Concatenate LOAD 'Forecast' as Document, Dates, Dates as TMPDates, Quantity Resident Main Where Document = 'Shipped' and Quantity <> 0 AND NOT EXISTS (TMPDates, Dates);

Re: New table with values from another tables

bamtor — Thu, 18 Jun 2020 12:26:27 GMT

Yes, it works, thank you @Vegar !