topic Re: Tables Structure in QlikView

Tables Structure

mustafaelryah — Mon, 24 Aug 2015 12:02:32 GMT

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

Re: Tables Structure

sujeetsingh — Mon, 24 Aug 2015 12:32:57 GMT

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

Re: Tables Structure

mustafaelryah — Mon, 24 Aug 2015 12:47:53 GMT

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?

Re: Tables Structure

alex_millan — Mon, 24 Aug 2015 12:52:28 GMT

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

Re: Tables Structure

Mon, 24 Aug 2015 13:05:33 GMT

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

Accounts:

Select * from Accounts;

Loosen table Accounts;

Try it

Re: Tables Structure

Anonymous — Mon, 24 Aug 2015 13:18:26 GMT

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

Re: Tables Structure

mustafaelryah — Mon, 24 Aug 2015 13:20:45 GMT

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?

Re: Tables Structure

alex_millan — Mon, 24 Aug 2015 13:30:20 GMT

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

Re: Tables Structure

mustafaelryah — Tue, 25 Aug 2015 06:58:51 GMT

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 !!.

Re: Tables Structure

mustafaelryah — Tue, 25 Aug 2015 07:02:32 GMT

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

Re: Tables Structure

mohammadkhatimi — Tue, 25 Aug 2015 07:06:52 GMT

Hie...

Try to do by link table...

PFA how to do link table jst go through it...

Hope this will helps you...!!!

Regards,

Mohammad

Re: Tables Structure

sujeetsingh — Tue, 25 Aug 2015 07:15:11 GMT

I think you should go through all the posts .

Re: Tables Structure

mustafaelryah — Tue, 25 Aug 2015 08:20:27 GMT

Here below from my real code:

Account_Budget is a (Budget_Cat)

account is a (AccountID)

company is (CompanyID)

when I run it I faced an error that the account field is not existing and also company is not exist.

Re: Tables Structure

mustafaelryah — Tue, 25 Aug 2015 08:21:34 GMT

Also this error happened when I tried the load of load.

Re: Tables Structure

alex_millan — Tue, 25 Aug 2015 08:25:47 GMT

Hi again,

Are you loading the table 'Chart_Of_Accounts' before to try to load the map table, aren't you?

And have you checked that the name of the fields that you load in the map table matches perfectly (case sensitive) the fields in 'Chart_Of_Accounts'?

Also, it would be of great help if you can attach your script (or only the part relative to these tables) to check if you have any inconsistence.

Re: Tables Structure

mustafaelryah — Tue, 25 Aug 2015 09:05:35 GMT

Thaanks a lot , I find that I used the company and account to create a key already like a below

company&'-'&account as %Company,

I load it again and worked successfully both of them load and map

and the linking now it seems is good,

but the results on chart confused me , I will send you the data to see it

Re: Tables Structure

mustafaelryah — Wed, 26 Aug 2015 07:56:09 GMT

Dear Alex ,

Kindly find below here the data and the qv file

Dropbox - QV.rar

Can you check the budget chart, After I linked the actual with budget in table , I can't bring it both together in one chart to compare it .

Regards

Re: Tables Structure

alex_millan — Wed, 26 Aug 2015 08:50:34 GMT

Hi Mustafa,

Give me some time to have a look deeply but, at first sight, I can tell you that the data in the key-field #Budget is different in the two tables:

- the part corresponding to the date doesn't have the same format in the different tables

- the mapping is not working fine

I'll take a look later and come back on this in a while

Re: Tables Structure

mustafaelryah — Wed, 26 Aug 2015 09:00:17 GMT

Dear Alex,

I appreciate your strong help to me, I do the linking for 3 days and I am so confused. Take your time and I am waiting for you.

Regards.

Re: Tables Structure

alex_millan — Wed, 26 Aug 2015 09:42:56 GMT

Hi again,

To get the date format aligned for the composite-key, change the script for the table 'Budget' to:

LOAD

Company & '_' & Account_budget & '_' & Date(date,'DD/MM/YYYY') As #Budget,

Company, Account_budget, Value, date

FROM $(vPath)\BI Budget Database.xlsx(ooxml, embedded labels, table is Budget);

Also, I was trying to validate the table 'Chart_Of_Accounts' and it seems to be a problem with the field 'Account_Budget' as it's not being calculated. You must check it, that's a beast of a nested conditional

Possibly, when you have this field properly calculated, the mapping would work fine.

Once you solve it, let me know it you keep having problems with the mapping part.

Cheers!