script for modelisation

[demoustier]

Hi Qlikers !!!!!
I'm facing an issue with modelisation and hope you can help.

I have a table BUDGET with 7 fields (ENTITY, PERIOD, ANALYSIS, ACCOUNT,CAT,AMOUNT,VERSION)

I have a table <key with 5 fields existing in BUDGET (ENTITY, PERIOD, ANALYSIS, ACCOUNT,CAT)

all ENTITY, PERIOD, ANALYSIS, ACCOUNT,CAT combination existing in BUDGET are existing in KEY but  there are some ENTITY, PERIOD, ANALYSIS, ACCOUNT,CAT combination existing in KEY but not in BUDGET.

Challenge is to add to BUDGET table all ENTITY, PERIOD, ANALYSIS, ACCOUNT,CAT combination existing in KEY but not in BUDGET, for all existing VERSION in BUDGET and with 0 as AMOUNT

Number of VERSION is not fix and will increase.

Hereby an example (in reality, KEY has more than 100.000 lines and BUDGET more than 50.000):

Any ideas ????

Many thanks !!!!!

[ArnadoSandoval]

Hi @demoustier 

You need to explain your business rules here, I gave it a try but it did not work because you failed to clarify the BR for the Key with an identify of 'X' is the only one appended to the results, why is it so? Why the keys: A + B + C + R + F + V1 and A + B + C + D + F + V2 were not include in the result table?

Once you explain this rule, we may be able to propose a solution.

Hope this helps,

[ArnadoSandoval]

Hi @demoustier 

My question above still stand, here I am posting the load script that might resolve your issue.

Budget:
LOAD
    Entity,
    Period,
    Analysis,
    Account,
    Cat,
    Version,
    Amunt,
    'Budget' As Source
FROM [lib://Sample_Data/Script-Model.xlsx]
(ooxml, embedded labels, table is Budget);

NoConcatenate

Key:
LOAD
    Entity,
    Period,
    Analysis,
    Account,
    Cat,
    'V1'   As Version,
    'Key'  As Key_Source
FROM [lib://Sample_Data/Script-Model.xlsx]
(ooxml, embedded labels, table is Key)
Where Not Exists(Entity);

Concatenate(Key)
LOAD
    Entity,
    Period,
    Analysis,
    Account,
    Cat,
    'V2'   As Version,
    'Key'  As Key_Source
Resident Key;

NoConcatenate

Result:
Load 
    Entity,
    Period,
    Analysis,
    Account,
    Cat,
    Version,
    Amunt,
    Source
Resident Budget;

Outer Join(Result)
Load
    Entity,
    Period,
    Analysis,
    Account,
    Cat,
    Version,
    Key_Source
Resident Key;

Drop Table Budget;
Drop Table Key;

hth