Get all items that weren't sold

[Yousef_Amarneh]

Hello,

I have 2 tables, the first one is "Sales Transactions" and the second on is "Inventory", I need to get all items that weren't sold when thier quantity in the inventory is greater than zero.

Thanks.

[sujeetsingh]

Hi,

Just join the two tables with key

And with set analysis you can get what you want cant you please give details of the fields in both tables

[Yousef_Amarneh]

Thank you for your reply, please see the attached sample of data.

[sujeetsingh]

Hi,

see the sample

Here you can get the complete data with one table and bnow you can filter the data according to your need.

Or explain that to me i will do it.

[Yousef_Amarneh]

Hi,

Could you please use the data that I attached in the excel? I need the items that weren't sold, so the result should be item 9 and 10.

Thanks

[sujeetsingh]

See this sample

[Yousef_Amarneh]

Thank you

[#8]

This will return you only the items you wanted:

Table1:

LOAD * INLINE [

    ItemCode, SalesQu, SalesDate, SalesAmount, Flag

    1, 12, 06/12/2012, 100, 1

    2, 1, 06/12/2011, 200, 1

    3, 2, 06/12/2012, 120, 1

    4, 4, 06/12/2012, 300, 1

];

join

Table2:

LOAD * INLINE [

    ItemCode, transDate, QuantityINOut, transType

    1, 12/12/2009, 20, in

    2, 12/12/2009, 10, in

    3, 12/12/2009, 10, out

    4, 12/12/2009, 30, out

    9, 12/12/2009, 100, out

    10, 12/12/2009, 110, out

];

FinalTable:

NoConcatenate

load *

Resident Table1

where Flag <> 1;

drop table Table1;

[Yousef_Amarneh]

I need this data after a specific date that the user will select, for example I need a report represent all items that weren't sold from 6/10/2013 till now, this is another problem

[paulyeo11]

Hi Yousef

Can you share with me what are the reasons you need to know those items in inventory not sold before ?