Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

ch_riadh
Contributor

Data modeling

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.

9 Replies
Not applicable

Re: Data modeling

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

Hi Riadh,

Can you please share the sample file.

Re: Data modeling

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

Not applicable

Re: Data modeling


Hi

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

Thanks,

Ashok.

Re: Data modeling

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

Pricesum.png

Regards

Anand

chinnakatikisg
Valued Contributor

Re: Data modeling

Hey,

PFA qvw as you required.

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

BR,

Chinna.

ch_riadh
Contributor

Re: Data modeling

Please Can you Attach the QVW ?

Many THANKS

ch_riadh
Contributor

Re: Data modeling

Thank you very much.
it s work fine.

Re: Data modeling

Hi,

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

Regards

Anand

Community Browser