Data Load Script - Help Needed

[Anonymous]

Problem: 

I have an expression in my data set that determines what the final order age is based on multiple factors.  I need to create age buckets off of the result of this field.  I could do it but it would be extremely time consuming and complicated.  This is a local app that I update via 2 spreadsheets in order to create the visuals.

A co-worker suggested I use a set within the load script to create the final order age but for some reason if will not work.  I also tried loading it inside one of the table loads and the OrderAge worked, however, when I tried to create an expression based off the field, it defaulted to the else statement and apparently did not view the results of the order age field as numbers...I think.

The first logic is

If(ItemCleaned > LastWorkDate(OrderCreated,CincLeadTime)+1,Networkdays(ItemCleaned,Today()),

If(Crd > LastWorkDate(OrderCreated,CincLeadTime)+1,Networkdays(Date(FirstWorkDate(Crd,(CincLeadTime))-1),Today()),

If(IsNull(ItemCleaned),'Unclean',Networkdays(ItemCleaned,Today()))))

This works fine as an expression but trying to create Age buckets off this is what I am trying to avoid.

The age bucket logic would be something like this...

=If(networkdays (OrderAge, Today()) > 0 and networkdays (OrderAge, Today()) <=24,'0-24 Days',

If(networkdays (OrderAge, Today()) >= 25 and networkdays (OrderAge, Today()) <= 29,'25-29 Days',

If(networkdays (OrderAge, Today()) >= 30 and networkdays (OrderAge, Today()) <= 44,'30-44 Days',

If(networkdays (OrderAge, Today()) >= 45 and networkdays (OrderAge, Today()) <= 60,'45-60 Days',

If(networkdays (OrderAge, Today()) >= 61,'61+ Days','')))))

Any help is greatly appreciated!!

[gagan_bhasin]

Hi Kyle,

Let's say you are getting the final order age in a column, 'Order Age'. Then, just create a table using inline load, wherein you specify various Day Ranges that you might expect like:

Load * Inline

[Key     StartDay,      EndDay

1,     0,      24

2,     25,      29

3,     30,     44

4,     45,     60

]

Post that you may just need to create an IntervalMatch, specifying join between this table and the table that contains 'Order Age'.  For example:

Load Key, OrderAge

from .... <table_name>

InnerJoin

IntervalMatch (OrderAge, Key)

Load * Inline

[Key     StartDay,      EndDay

1,     0,      24

2,     25,      29

3,     30,     44

4,     45,     60

]

The above script will then generate a single table with the specific start and end dates mapped. Then all you'd need to create is a separate column where you concatenate the corresponding start and end dates to show it in the age bucket format as mentioned.

[Anonymous]

I can do this and it makes sense, however, the first problem I am running into is that the logic I have to create order age isnt being seeing as a number, therefore, the match may not work.

I will try and post back.

Thanks

[Anonymous]

Thank you,

I had been staring at the data to long and realized that the second logic was trying to find network days off a number not a date *sigh* that is what I get working 20 hour days.

Thanks all

[Anonymous]

Curious as to why your OrderAge is not numeric, can you elaborate.