topic Re: Data modeling in QlikView

Data modeling

ch_riadh — Tue, 04 Nov 2014 06:56:35 GMT

Hi Guys.

I have table: Invoice_Table:

LOAD

Vehicle_Number, { 27170 , 12345, .....}

Invoice_Sequence, { 1,2,3}

// One Vehicle can be sold and credit and re-Sold again, So every time sold it have a Invoice sequence.

// i concatenated this 2 fields so i can count How many time The vehicle:

Vehicle_Number & Invoice_Sequence as Key_Vehicle:

27170-1

27170-2

12345-1

Count(Vehicle_Number) ----> i got 2 vehicles.it's not true.

count(Key_Vehicle) ----> i got 3 vehicles. it Work Fine.

Now i have to Sum the Price But The Price Field is in an Other Table ' Vehicle_Table'.

LOAD

Vehicle_Number, { 27170 , 12345, .....}

Price

i link the 2 tables with Vehicle_Number, it's the only key i have.

When i Put the Sum Expression : Sum(Price) -----> it gave me the price of 2 Vehicles.

But I need the Price for the 3 Vehicles. i want it to depend of the Key_Vehicle, not The Vehicle_Number Field.

Thanks in advance.

Re: Data modeling

Tue, 04 Nov 2014 07:13:56 GMT

Hi ,

Bring the Invoice sequence field into 'Vehical_Table' by using mapping . then crate a combosite key by using vehical no& Invoice sequence.

take that key as a dimension you will get. other wise u can take those two dimensions as well.

hope this helpful.

Thanks,

Ashok.

Re: Data modeling

avinashelite — Tue, 04 Nov 2014 07:17:56 GMT

Hi Riadh,

Can you please share the sample file.

Re: Data modeling

its_anandrjs — Tue, 04 Nov 2014 07:18:13 GMT

Hi,

Try to join this two table and make single table and then check what come for the SUM(Price) or you can map the second table

TabOne:

LOAD Vehicle_Number,Invoice_Sequence ,Vehicle_Number&Invoice_Sequence as Key_Vehicle;

LOAD * Inline

[

Vehicle_Number, Invoice_Sequence

27170,1

27170,2

12345,1

];

Join

TabTwo:

LOAD * Inline

[

Vehicle_Number,Price

27170,158522

12345,256358

];

Regards

Anand

Re: Data modeling

Tue, 04 Nov 2014 07:32:02 GMT

PFA............

Thanks,

Ashok.

Re: Data modeling

its_anandrjs — Tue, 04 Nov 2014 07:36:24 GMT

Hi,

Have a look at simple example

Tab1:

LOAD Vehicle_Number,Invoice_Sequence ,Vehicle_Number&Invoice_Sequence as Key_Vehicle;

LOAD * Inline

[

Vehicle_Number, Invoice_Sequence

27170,1

27170,2

12345,1

];

Join

Tab2:

LOAD * Inline

[

Vehicle_Number,Price

27170,158522

12345,256358

];

And after join

Regards

Anand

Re: Data modeling

Anonymous — Tue, 04 Nov 2014 07:38:58 GMT

Hey,

PFA qvw as you required.

Hope this helps and pls mark if it is the solution u r looking for.

BR,

Chinna.

Re: Data modeling

ch_riadh — Tue, 04 Nov 2014 07:57:08 GMT

Please Can you Attach the QVW ?

Many THANKS

Re: Data modeling

ch_riadh — Wed, 05 Nov 2014 06:20:56 GMT

Thank you very much.
it s work fine.

Re: Data modeling

its_anandrjs — Wed, 05 Nov 2014 06:28:29 GMT

Hi,

If you got correct answer from the post then mark the thread as correct for reference and please close the thread.

Regards

Anand