Linking tables question

[ecabanas]

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

[avinashelite]

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

[ecabanas]

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

[avinashelite]

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

[PradeepReddy]

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

[ecabanas]

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

[PradeepReddy]

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...

[ecabanas]

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,

[avinashelite]

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

[PradeepReddy]

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