topic Re: Data modeling in star in QlikView

Data modeling in star

Fri, 25 Jul 2014 12:08:24 GMT

Hello,

I am writing to you because I want to model my app like a star model.

Actually, my structure is like :

I think it will be good to transform it with the method of hollow table to obtain :

But what I don't understand is how can I do that, considering that I already have relations between tables, like the field "Section".

Do I have to rename them ?

Thanks for your help

Re: Data modeling in star

crusader_ — Fri, 25 Jul 2014 12:44:09 GMT

Hi, Olivier

Firstly unite Invoice tlb and InvoiceLine tlb. You can use join or ApplyMap().

What's the semantic difference between OrderLine and OrderLine2 tlb?

As I understood Order tlb - should be Dimension in your datamodel.

Also Orders, Invoices and Budget are different semantical entities, but technically you can concatenate them using additional field Flag to recognize definite entity.

Hope this helps.

Kind regards,

Andrei

Re: Data modeling in star

fkeuroglian — Fri, 25 Jul 2014 13:03:44 GMT

Hi Andrei

First of all try to understando what you want and what info does your tables have

you have to determinate what would be your fact table and then determinate the dimension you have

invoice and invoice line could be join in the same table but ORDER LINE, ORDER LINE 2 AND ORDER? WHAT IS THE DIFFERENCE between this table?

please try to explain that to be easier for us to help you

Thanks

Fernando

Re: Data modeling in star

Fri, 25 Jul 2014 13:10:52 GMT

Hello,

Thanks for your responses.

I will join invoice and invoice line with applymap.

In fact, Order, Order Line and Order Line 2 come from different tables. If I concatenate all the three, the result isn't correct.

Order

Contains the order number, supplier and designation (it is a concatenate of two tables with supplier and designation).

Order Line

Contains the order number, section (like department), order line reference (line of order) and the total of this line.

Order Line 2

Contains the number and the ligne too, but what I need is the comment 1 and 2.

Thanks again !

Re: Data modeling in star

crusader_ — Fri, 25 Jul 2014 13:57:38 GMT

Hm...

Why don't you join all Order tables? Is order line a grain in your datamodel?

Could it be multipled by other fields, I mean could one order line belong to different orders or could it have 2 values for Comment 1 field etc ?

If not, you can join all three these tables I suppose.

Andrei

Re: Data modeling in star

Tue, 29 Jul 2014 13:07:45 GMT

Hello Andrei,

Thanks for your reponse.

I've made the apply map for the two invoices tables.

It will be very good if I can join all the three Order tables yes.

To respond to your question, I don't know what it means "order line a grain in your datamodel", but one order line only belong to one order or one comment field.

Can you please explain me how can I join the three table ?

Is it by concatenate or other thing ?

Thank you !

Re: Data modeling in star

crusader_ — Wed, 30 Jul 2014 07:07:23 GMT

Hello,

Firstly, "grain" means the least unique part of your datamodel, for example:

================================

================================

| 1 | Pencil A | usual | 2 | 0.1 | 0.2 |

| 1 | Pencil A1 | usual | 2 | 0.12 | 0.24 |

| 1 | Pencil B | auto | 5 | 0.2 | 1 |

In this table grain is the string with unique combination OrdNo&Product&Type.

In your case I asked about next case:

======================================================

======================================================

| 1 | A | Smth | 200.00 | ABC

| 1 | A | Smth | 200.00 | CBA

======================================================

To join tables you just write "left join" before your LOAD statement in script.

Order:

LOAD

[Order Number]

, [Order Supplier]

, [Order Designation]

from ...xls or SQL;

OrderLine1:

left join

LOAD

[Order Number]

, [Section]

, [Order Line Reference]

, [Order Line Amount]

from ....;

OrderLine2:

left join

LOAD

....

FROM...;

The joining will be made by the same fields name, so be sure about combination of field names when you make joining.

Hope this helps.

Andrei

Re: Data modeling in star

Thu, 31 Jul 2014 10:08:29 GMT

Thanks Andrei for your response.

I did the join, and I also found and other field in invoice (order number) that permit to do the link between the order and the invoice.

This is the schema I have. But to avoid a loop, in the OrderLine table I changed the name "Section" for "Section Line". So, I don't have the link with the Budget table anymore...

Do you have any idea to correct this ?

Thanks !

Re: Data modeling in star

Yousef_Amarneh — Thu, 31 Jul 2014 10:21:57 GMT

It would be better if you use Link table,

LinkTable:

Load Distinct [Order Number Line] & '-' & Section as Key1,

[Order Number Line],

Section

Resident OrderLine;

Concatenate(LinkTable)

Load Distinct [Order Number Line] & '-' & [Order Number] as Key2,

[Order Number Line],

[Order Number]

Resident Order;

Concatenate(LinkTable)

Load Distinct [Order Numbe] & '-' & Section as Key3,

[Order Number],

Section

Resident Invoice;

and Do the concatenated Keys in all tables also.

Good Luck.

Re: Data modeling in star

mahesh_agrawal — Thu, 31 Jul 2014 10:26:16 GMT

Hi,

Will it work for u??

Re: Data modeling in star

Thu, 31 Jul 2014 13:14:42 GMT

Hello,

Thanks for your reponse.

Can you help me for a thing. In fact, OrderLine or Order are join tables.

To do the key on a join table, I have to reload exclusively the complete table ?

Because if I do something like :

Order_tmp:

LOAD

[Order Number],

[Order Supplier];

SQL

SELECT FROM;

Left Join

Order_tmp2:

LOAD

[Order Number Line],

[Order Designation];

SQL

SELECT FROM;

Order:

LOAD

[Order Number],

[Order Supplier],

[Order Number Line],

[Order Designation]

Resident Order_tmp;

Drop table Order_tmp;

---

With this I can get back the Order table in my script.

Do you know how to do ?

Thanks lot !

Re: Data modeling in star

crusader_ — Thu, 31 Jul 2014 13:35:22 GMT

Hello Olivier,

If you do such thing

Order_tmp:

LOAD

[Order Number],

[Order Supplier];

SQL

SELECT FROM;

Left Join

Order_tmp2:

LOAD

[Order Number Line],

[Order Designation];

SQL

SELECT FROM;

you will get outer join instead of left join.

Also try to modify your datamodel as Mahesh suggested if possible on your dataset.

Regards,

Andrei

Re: Data modeling in star

Yousef_Amarneh — Fri, 01 Aug 2014 14:20:12 GMT

Are you sure that your join is correct? if so then you have to make the key after loading the complete table.

Re: Data modeling in star

Fri, 01 Aug 2014 14:23:39 GMT

Hello,

Really not sure no

I'm stuck with this program, I don't have the time for the moment to see it, but I will try to resume and see with key or with mahesh proposal !

Thanks!

Re: Data modeling in star

Yousef_Amarneh — Fri, 01 Aug 2014 14:28:49 GMT

if you want just send me the a script that extracting the data from the data source as is without join and I will try to make it for you