Link to Budget Table Dinamically

[marcel_olmo]

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.

[maxgro]

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;

[Gysbert_Wassenaar]

See attached qvw.

[marcel_olmo]

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 :

[marcel_olmo]

Maybe here I show my example more clear of what I'm trying to do :

Populate Table Dinamically

[#6]

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;