topic Linking tables question in QlikView

Linking tables question

ecabanas — Mon, 21 Mar 2016 09:07:20 GMT

Hi all,

I have a question because now i'm blocked. I have a document that analyze the sales from my company. This analysis is made itemid by itemid and with a calendar...

Now I have a Stock evolution with a daily snapshot itemid by itemid.

I want to see the sales analysis and see wich was the stock situation by this date, and I don't know how to put the stock data in the document.

See the structure attached

Many thank's guys

Re: Linking tables question

avinashelite — Mon, 21 Mar 2016 09:21:47 GMT

You could link the saleLine table with the Stock table using the itemId

Re: Linking tables question

ecabanas — Mon, 21 Mar 2016 09:42:52 GMT

Hi Avinash

No, it is not possible, because imagine that one day there is stock for an Item but no sales, the stock value will not appears :-s

Many thank's

Eduard

Re: Linking tables question

avinashelite — Mon, 21 Mar 2016 09:48:52 GMT

In that case you need to identify the most appropriate key according to your requirement , which your table I would prefer using the Data field i.e may be the created date field...so your complete model would be based on the date

check your requirement choose the field

Re: Linking tables question

PradeepReddy — Mon, 21 Mar 2016 10:03:22 GMT

try some thing like this...

Temp_Data:

Load SalesBoid,

created_Field as Date_Item

resident SalesTable

inner join

Load SalesBoid,

ItemID

resident SalesLine;

Avoid the circular loops with the SalesLineTable.

Now the link would between Stock Table, Sales Table thru Temp_Data Table

Re: Linking tables question

ecabanas — Mon, 21 Mar 2016 10:12:55 GMT

Hi pradeep,

I don't understand why to do an inner join with salesline....how I link Stock with it?

Sorry about this may be stupid answer 😉

Many thank's

Eduard

Re: Linking tables question

PradeepReddy — Mon, 21 Mar 2016 10:33:07 GMT

see the attached excel sheet for the expected/proposed data model..

Some time the proposed solutions would not work or not clear to others. Here every one is help to others..

So put it in mind before posting a comment...

Re: Linking tables question

ecabanas — Mon, 21 Mar 2016 10:52:21 GMT

pradeep,

Sorry if I did no explain correctly, I said stupid answer, but MY answer, never your, please sorry if you understand in the wrong way,

Re: Linking tables question

avinashelite — Mon, 21 Mar 2016 10:59:34 GMT

inner is to limit the data with intersection between the 2 tables i.e. if you use inner join it will consider only the common key between two tables and will ignore the rest ...

in your case i don't think inner join is required

Re: Linking tables question

PradeepReddy — Mon, 21 Mar 2016 11:03:37 GMT

I know... but, suggested you to use proper words in posts...

Re: Linking tables question

PradeepReddy — Mon, 21 Mar 2016 11:06:52 GMT

can u post sample data/App, so that it would be easy to suggest a solution...

Re: Linking tables question

ecabanas — Mon, 21 Mar 2016 12:08:36 GMT

Hi Avinash

Yes you are right, the query did not work as you mentioned

Re: Linking tables question

Digvijay_Singh — Mon, 21 Mar 2016 12:23:22 GMT

I think you can give one try by creating composite key of itemId and Date in both Salesline and Stock, give same name in both tables so they will be link, remove ItemId from stock or rename it to different name to avoid synthetic key.

And then left join(salesline) with stock table, so you will have all the records of stock which are present in Salesline.

Re: Linking tables question

avinashelite — Mon, 21 Mar 2016 12:26:26 GMT

you need have the date formats in the same format ...could you please share the script how your linking the tables ??

you can set the date format using the date() function

like Date(date_field,'DD-MM-YYYY')

Re: Linking tables question

ecabanas — Mon, 21 Mar 2016 12:29:08 GMT

Hi Digvijay

Yes, you are right, but I want to see all my stock available, the units and if it has been or not sales, your proposal is very nice but erase all the stock units not sold...

WOW it's a very complicated problem.

many thank's for your help

Re: Linking tables question

Digvijay_Singh — Mon, 21 Mar 2016 12:50:41 GMT

Then don't join and just create the link as I suggested, see if it serves the purpose.

Re: Linking tables question

stabben23 — Mon, 21 Mar 2016 13:05:55 GMT

Hi, have a look at this document: Generating Missing Data In QlikView. Maybe you should try to generat all dates in month so you get the same value on each row in Your Stock table.