Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
wanyunyang
Creator III
Creator III

Get Item Status at particular time

I have two tables:

Item table with: Item, OrderDate

ItemStatus table, which has item status change information :

Item, StatusChangeDate1, OldStatus1, NewStatus1

(If status has changed multiple times, may also have: Item, StatusChangeDate2, OldStatus2, NewStatus2)


I'm trying to link these two tables together. By selecting an OrderDate, I want it give me the item status at that time.


How should I build the model? Any advice helps! Thank you in advance!

5 Replies
kingsley101
Contributor III
Contributor III

Hi Wanyun.

Are you saying that a new date and status field are created everytime a status is changed?

wanyunyang
Creator III
Creator III
Author

Hi Kingsley, yes you are right.

kingsley101
Contributor III
Contributor III

I think to build this, you should consider having a field called "Item", another field called "Modified date" and another field called "Status"... This would be a temporary table that you load to make your final product...

If you aren't needing the historical data in the model, then you can do this:

Left join(status)

Load Item,

Max([Modified date]) as [Modified date]

Resident status

Group by Item;

That way you will only have the latest status for each item

But if you are wanting to keep the historical data in the model, then you don't need the bold section.

Thanks

Kingsley

wanyunyang
Creator III
Creator III
Author

Hi Kingsley,

Yes I already have those in my ItemStatus table. The only thing I can't achieve now is by selecting an order date, how can I be able to see the item status at that time.

kingsley101
Contributor III
Contributor III

Hi Wanyun.

So this is what you'll need to do:

Load your order table (the date field is called OrderDate)

Load your Item Status table (All Dates must be in one field called Date, All Status in 1 field called Status)

(These 2 tables need to associate on the field Item)

Create a straight table with the Dimensions:

Item,

OrderDate,

Status

=IF(Date=Date(AGGR(Max(IF(Date<=OrderDate,Date)), Item, OrderDate),'DD/MM/YYYY'),Date) //Make sure this calculated dimension is suppressed when null

You can hide the calculated dimension (or any other dims) on the presentation tab of the chart properties.

Thanks