Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi guys,
I have a budget Table to link into a master Table, like this :
Facts Table :
Budget Table :
As you see, there are blank fields in the Customer column. This means that it needs to be populated with the Cust2 and Cust3 values to link this table properly with the Facts Table.
The point is, if the column is not populated, it has to be populated with the possible values of customer.
How can this be done?
I've attached a simple example.
Regards, Marcel.
In your case you have the same dimensions in both the tables
I'll concatenate the tables and add a type field (budget / movement)
// movements
Fact:
LOAD Year,
Month,
Customer,
Product,
Amount,
'Mov' as Type
FROM
Example.xlsx
(ooxml, embedded labels, table is Movements);
// budget, add missing customer
Tmp: load Distinct Customer Resident Fact;
join (Tmp)
LOAD Year,
Month,
//Customer,
Product,
Budget,
'Bdg' as Type
FROM Example.xlsx
(ooxml, embedded labels, table is Budget)
Where len(trim(Customer))=0;
// budget, customer
Concatenate (Tmp)
load
*,
'Bdg' as Type
FROM Example.xlsx
(ooxml, embedded labels, table is Budget)
Where not len(trim(Customer))=0;
// concat budget to movement
Concatenate (Fact)
load * Resident Tmp;
DROP Table Tmp;
See attached qvw.
Hi gwassenaar ,
thanks for the try! The point here is that you can't populate with all the possible customer values, only the ones that were not populated before.
Here's what I mean if I select Year 2015, Month 1 and Customer Cust1 :
Maybe here I show my example more clear of what I'm trying to do :
Try Like Below:
FactTable:
LOAD * from FactTable;
BudgetTable:
LOAD
Year,
Month,
IF(IsNull(Cust),'Left','Cust') AS TempCust,
Prod,
Budget
From BudgetTable;
//Get List of Cust from Budget
TempCust:
LOAD
Distinct TempCust AS TempCust1
TempCust as Cust
Resident BudgetTable
where TempCust <> 'Left'
;
// Concatenate Left cust from Fact to TempCust
Concatenate(TempCust)
LOAD
distinct Cust
'Left' AS TempCust1
Resident FactTable
where not exists( TempCust1,Cust)
;
// Perform Join
Left Join(BudgetTable)
Load
TempCust1 AS TempCust,
Cust
Resident TempCust;
Drop Table TempCust
DropField TempCust;