topic Re: Link to Budget Table Dinamically in QlikView

Link to Budget Table Dinamically

marcel_olmo — Fri, 13 Nov 2015 12:21:26 GMT

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.

Re: Link to Budget Table Dinamically

maxgro — Fri, 13 Nov 2015 12:38:45 GMT

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;

Re: Link to Budget Table Dinamically

Gysbert_Wassenaar — Fri, 13 Nov 2015 12:53:26 GMT

See attached qvw.

Re: Link to Budget Table Dinamically

marcel_olmo — Fri, 13 Nov 2015 13:11:30 GMT

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 :

Re: Link to Budget Table Dinamically

marcel_olmo — Fri, 13 Nov 2015 13:55:29 GMT

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

Populate Table Dinamically

Re: Link to Budget Table Dinamically

Fri, 13 Nov 2015 15:31:15 GMT

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;