New table with values from another tables

[bamtor]

 

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?

[Vegar]

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;

[bamtor]

Hi, @Vegar!

Thanks for reply.

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.

[Vegar · Accepted solution]

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);

[bamtor]

Yes, it works, thank you @Vegar !