topic Re: NetWorkDays in QlikView

NetWorkDays

Anonymous — Sun, 10 Aug 2014 06:02:49 GMT

Hi All,

I Have the following scenario.

If customer place the order on Saturday or Sunday, the OrderDateTime should show as The Monday's Date and the time should show as '00:00:00'.

For eg: If the order was placed on 08-10-2014(Sunday), The OrderDateTime should show as 08-11-2014 00:00:00

Any idea?

Re: NetWorkDays

MK_QSL — Sun, 10 Aug 2014 06:26:15 GMT

Load

TimeStamp(Timestamp#(OrderDate,'DD-MM-YYYY hh:mm:ss')) as OriginalOrderDate,

TimeStamp(IF(WeekDay(Timestamp#(OrderDate,'DD-MM-YYYY hh:mm:ss')) = 'Sat',

TimeStamp(Date(Floor(Timestamp#(OrderDate,'DD-MM-YYYY hh:mm:ss')))+2),

IF(WeekDay(Timestamp#(OrderDate,'DD-MM-YYYY hh:mm:ss')) = 'Sun',

TimeStamp(Date(Floor(Timestamp#(OrderDate,'DD-MM-YYYY hh:mm:ss')))+1,),Timestamp#(OrderDate,'DD-MM-YYYY hh:mm:ss'))),'DD-MM-YYYY hh:mm:ss') as NewOrderDate

Inline

[

OrderDate

08-08-2014 10:10:00

09-08-2014 10:10:00

10-08-2014 10:10:00

11-08-2014 10:10:00

];

Pardeep : Change Dates as per your system or database data.. Looks like your Dates are MM-DD-YYYY... while in my above example, it is DD-MM-YYYY... so change accordingly...

Re: NetWorkDays

aveeeeeee7en — Sun, 10 Aug 2014 11:54:36 GMT

HI Manish Kachhia

This is something called as Incredible Stuff. Just loved it.

Good Job.

Regards

Aviral Nag

Re: NetWorkDays

MarcoWedel — Sun, 10 Aug 2014 15:21:24 GMT

Hi,

another solution might be:

If(WeekDay(OrderDateTime)>=5, Timestamp(Floor(OrderDateTime+7-WeekDay(OrderDateTime)), 'MM-DD-YYYY hh:mm:ss'), OrderDateTime)

LOAD *,
    WeekDay(OrderDateTime) as WeekDayOrderDateTime,
    WeekDay(OrderDateTimeAdjusted) as WeekDayOrderDateTimeAdjusted;
LOAD *,
    If(WeekDay(OrderDateTime)>=5, Timestamp(Floor(OrderDateTime+7-WeekDay(OrderDateTime)), 'MM-DD-YYYY hh:mm:ss'), OrderDateTime) as OrderDateTimeAdjusted;
LOAD Timestamp(Timestamp#('01/01/2014', 'MM/DD/YYYY')+250*Rand(), 'MM-DD-YYYY hh:mm:ss') as OrderDateTime
AutoGenerate 100;

hope this helps

regards

Marco

Re: Re: NetWorkDays

rwunderlich — Sun, 10 Aug 2014 23:23:24 GMT

Marco,

A simpler method to advance the time may be WeekStart():

If(WeekDay(OrderDateTime)>=5

,timestamp(WeekStart(OrderDateTime,1))

,OrderDateTime

) as OrderDateTimeAdjusted

-Rob

Re: NetWorkDays

MarcoWedel — Mon, 11 Aug 2014 02:30:12 GMT

Very nice, I didn't remember this function.

Thanks Rob

Regards

Marco

Re: NetWorkDays

Anonymous — Mon, 11 Aug 2014 06:34:24 GMT

Thanks Rob

Its Working