Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mustafaelryah
Creator
Creator

Tables Structure

Dear All,

I have 3 tables:

1-Accounts

     1.1 AccountID

     1.2 (formula (AccountID)) as Budget_Cat..... # Calculated Column from the above one

     1.3 CompanyID

2-Buget

     2.1 Date

     2.2 Budget_Cat

     2.3 CompanyID

  

3.Transaction

     3.1 AccountID

     3.2 Debit

     3.3 Credit

     3.4 Date

     3.5 Company ID

  

I need to link  the Budget table with the Account table  (Budget_Cat, CompanyID) and Transaction table  (CompanyID, Date)

without looping or Syntactic keys

# note that the Budget_Cat is calculated field.

Regards

1 Solution

Accepted Solutions
alex_millan
Creator III
Creator III

Find attached the text files with the script for each tab.

With this script, the mapping works well.

But anyway, as there's not  a single common value between the two tables (transactions and budget), it seems no association is done, although it is. Check the attached image.

Use this script and check the values for the common key.

View solution in original post

33 Replies
sujeetsingh
Master III
Master III

just take help of Composite keys

This will help you

how to create composite key

Please follow Henric blog for some more technical stuffs

Synthetic Keys

mustafaelryah
Creator
Creator
Author

composite key is very helpful

so I can make one composite key on Budget Tabel CompanyID+Date+Budget Cat as Composite Field

but I use calculated column on the Account table ,

can I read the calculated column (Budget_Cat) on Transaction table to link it with the (date, companyID) ?

so I can put CompanyID+Date+BudgetCat as Compostite key

My main problem that I don't have the BudgetCat on Transaction table, can I move it using mapping?

alex_millan
Creator III
Creator III

To use your calculated field in your composite key, use precedent load

LOAD

CompanyID & Date & Budget_Cat As Key1,

---

;

LOAD

CompanyID,

Budget_Cat,

...

It's like doing a LOAD from your previous LOAD

And yes, you can do a mapping load from a resident table, for example:

Mapping LOAD Budget_Cat, CompanyID Resident YourTable;

HTH

Regards

Not applicable

Hi. You must use the for one or two tables construction like this.

Accounts:

Select * from Accounts;

Loosen table Accounts;

Try it

Anonymous
Not applicable

Hi Mustafa,

Try:

Accounts:

LOAD

Budget_Cat & CompanyID as BudgetCompanyID,

    AccountID as Acc_AccountID,

    (Calc(AccountID)) as Acc_Budget_Cat,

    CompanyID as Acc_CompanyID

   

    From ....;

   

Budget:

LOAD

     Budget_Cat & CompanyID as BudgetCompanyID,

     CompanyID & Date as DateCompanyID,

     Date,    

     Budget_Cat as Bud_Budget_Cat,

     CompanyID as Bud_CompanyID

From ....;

 

Transaction:

LOAD

      CompanyID & Date as DateCompanyID,

      AccountID  as Tran_AccountID,

      Debit,

      Credit,

      Date as Tran_Date,

      CompanyID as Tran_CompanyID

From ....;

Regards

Neetha

mustafaelryah
Creator
Creator
Author

It's nice solution thank you a lot

but I have a few complication ( Day field is on another table (transaction )) ?!

how can I do that

on Transaction table I have companyID and Date but I miss BudgetCat?

can I read the Budget_Cat on Transaction table? via mapping or something else? 

alex_millan
Creator III
Creator III

Yes Mustafa,

for example, if you have previously load BudgetCat and AccountID on your Accounts table (assuming there's only one BudgetCat for each AccountID), it would be like:

Accounts: LOAD AccountID, BudgetCat, CompanyID,... from ...;

Temp_Budget_Cat: Mapping LOAD BudgetCat, CompanyID Resident Accounts;

Transactions:

LOAD AccountID, ApplyMap('Temp_Budget_Cat',AccountID,'N/D') As BudgetCat, ....;

LOAD AccountID, Debit, Credit... from ....

Note that:

first, you load the Accounts data in order to have in memory the values for BudgetCat

second, you load a map table for these values

utterly, you use the map table in a precedent load when loading the transactions data

HTH

Regards

mustafaelryah
Creator
Creator
Author

My Dear,

     Your solution is very good, but I have already link between transactions and accounts and if I applied the double link you suggest I will fall on the loop !!.

mustafaelryah
Creator
Creator
Author

I will try to do it, and I will give you the feedback my dear.