Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Data modelling

Hi all,

I am facing a strong problem, I have 3 data tables

- sales, where each row shows: article_id, category_id, date, sale_quantity and some other data

- purchase, where each rows shows: article_id, category_id, date, purchase_quantity, and some other data

- warehouse, where each rows shows:article_id, category_id, date, stock_quantity and some other data

so, when I load my data the resulting tables are sales, purchase, warehouse and $Syn1.

In $Syn1 I can obvioiusly find the columns: article_id, category and date

Now, it seems to be ok. But I have to add some other tables:

- article_details: this will be linked to $Syn1 by article_id and offers to me other columns (article description, etc...)

- category_details: this will be linked to $Syn1 by category_id and offers to me other columns

- master_calendar: this will be linked to $Syn1 by date

I don't understand why selections on master_calendar seems to be ok, and selections on article_details are effective just on sales and not on purchase and warehouse. Let me explain: if I select an article_id the resulting rows of purchase are 0, while the resulting rows in sales are >0

I hope that someone can understand my problem and help me to find a solution, thank you in advance.

Carlo A. Babini

1 Solution

Accepted Solutions
Not applicable
Author

hi Carlo,

in my personal work, i always try to concatenate different transactional tables that usually contain a date field and other common fields, so i can link just one table to the dimension tables.

i can differenciate the concatenated tables by adding for each one for example a TrxType field and giving it a related description:

LOAD 'Sales' as TrxType,

date,

product_id,

etc...(rest of sales table)

Concatenate

LOAD 'Purchase' as TrxType,

date,

product_id,

etc...(rest of purchase table)

then use it in the set analysis expressions to get info for a specific transaction type

(for ex. Sum({$<TrxType = {'Sales'}>} Value) to get the sales amount

or Sum({$<TrxType = {'Warehouse'}>} Quantity) to get the stock quantity).

in this way i can apply a star schema design where i have a fact table (with the different types of transactions) and dimensional tables around (like the calendar, articles, clients, etc..).

the date field of the master calendar will be linked to the date field of the final transactional tables (which in fact represents the date field of each of the different transactional tables).

you can apply this in your case to avoid one major issue (in most of the cases) which is the synthetic keys that are probably causing the confusion in the selections/results.

i hope that this will help you create an easier structure.

View solution in original post

3 Replies
jolivares
Specialist
Specialist

To help you try to upload a version of your tables in a QVW.  Run with debug mode with 10 records.

Not applicable
Author

hi Carlo,

in my personal work, i always try to concatenate different transactional tables that usually contain a date field and other common fields, so i can link just one table to the dimension tables.

i can differenciate the concatenated tables by adding for each one for example a TrxType field and giving it a related description:

LOAD 'Sales' as TrxType,

date,

product_id,

etc...(rest of sales table)

Concatenate

LOAD 'Purchase' as TrxType,

date,

product_id,

etc...(rest of purchase table)

then use it in the set analysis expressions to get info for a specific transaction type

(for ex. Sum({$<TrxType = {'Sales'}>} Value) to get the sales amount

or Sum({$<TrxType = {'Warehouse'}>} Quantity) to get the stock quantity).

in this way i can apply a star schema design where i have a fact table (with the different types of transactions) and dimensional tables around (like the calendar, articles, clients, etc..).

the date field of the master calendar will be linked to the date field of the final transactional tables (which in fact represents the date field of each of the different transactional tables).

you can apply this in your case to avoid one major issue (in most of the cases) which is the synthetic keys that are probably causing the confusion in the selections/results.

i hope that this will help you create an easier structure.

Not applicable
Author

Thank you, I was not able to think about a star data modeling. But now everything seems to work, even if I am still checking the result.

thank you

Carlo A. Babini