Turning formulas/running total fields from crystal rpts to qlikview

[#1]

Hi all,

I am pretty new to qlikview and this is likely my first posting in here so please bear with me if its in the wrong area.

I am trying to convert some of my current crystal reports (ver 14) in to qlikview and having some issues with the conversion in either pre or post scripting and would like to know how to make some of this happen.

the one set of functions i need to convert work off of the following fields:

cr_prdate (datetime field, mm/dd/yyyy hh:mm:ss AMPM)

bk_audit_datetime (datetime field, mm/dd/yyyy hh:mm:ss AMPM)

the formulas I am using are..crystal syntax:

DayOfWeek -  weekdayname(dayofweek({cr_prdate})) -- this is used on the report or in formulas as @DayOfWeek

BookedDays - datediff("d", {bk_audit_datetime}, {cr_prdate}) -- shows up as @BookedDays

DayCheck - if {@DayOfWeek} = "Monday" and ({@BookedDays} <=4 and {@BookedDays} >=0) then "Exclude"

                  else if {@DayOfWeek} = "Tuesday" and ({@BookedDays} <=4 and {@BookedDays} >=0) then "Exclude"

                  else if {@DayOfWeek} = "Wednesday" and ({@BookedDays} <=4 and {@BookedDays} >=0) then "Exclude"

                  else if {@DayOfWeek} = "Thursday" and ({@BookedDays} <=4 and {@BookedDays} >=0) then "Exclude"

                  else if {@DayOfWeek} = "Friday" and ({@BookedDays} <=4 and {@BookedDays} >=0) then "Exclude"

                  else if {@DayOfWeek} = "Saturday" and ({@BookedDays} <=4 and {@BookedDays} >=0) then "Exclude"

                  else if {@DayOfWeek} = "Sunday" and ({@BookedDays} <=4 and {@BookedDays} >=0) then "Exclude"

                  else "Include"

I think that those are a good start.. I dont know if some of this should be processed in to their own fields during the script process or if they should be handled as a new entity once the data is loaded up.. it also seems datediff isnt a function in here and i tried nesting the if statement but it was returning everything as null.. so I missed something somewhere there too i guess..

Any and all help is greatly appreciated since these few funcs are pretty much used in different assortment (diff if statements etc) and are a backbone of some of my current reports I'd like to move.

Thank you all for your time too.. its appreciated!

[MayilVahanan]

Hi

Try like this,

          if ({@DayOfWeek} = "Monday" and ({@BookedDays} <=4 and {@BookedDays} >=0) ,'Exclude',

                   if ({@DayOfWeek} = "Tuesday" and ({@BookedDays} <=4 and {@BookedDays} >=0) ,'Exclude',

                   if ({@DayOfWeek} = "Wednesday" and ({@BookedDays} <=4 and {@BookedDays} >=0) ,'Exclude',

                if ({@DayOfWeek} = "Thursday" and ({@BookedDays} <=4 and {@BookedDays} >=0) ,'Exclude',

               if( {@DayOfWeek} = "Friday" and ({@BookedDays} <=4 and {@BookedDays} >=0) ,'',

                   if ({@DayOfWeek} = "Saturday" and ({@BookedDays} <=4 and {@BookedDays} >=0) ,'Exclude',                 

if( {@DayOfWeek} = "Sunday" and ({@BookedDays} <=4 and {@BookedDays} >=0) , 'Exclude'

                  , 'Include'

)))))))

Hope it helps

[#3]

I'll give that a shot, but how do you convert the datediff function when its a datetime field? 

[MayilVahanan]

HI,

Try like this

     Interval([bk_audit_datetime] - [cr_prdate],'D') as BookedDays

     WeekDay([cr_prdate]) as DayOfWeek

if (DayOfWeek= "Mon" and (BookedDays <=4 and BookedDays >=0) ,'Exclude', 

                   if (DayOfWeek = "Tue and (BookedDays <=4 and BookedDays >=0) ,'Exclude',

                   if (DayOfWeek = "Wed" and (BookedDays <=4 and BookedDays >=0) ,'Exclude',

                if (DayOfWeek = "Thur" and (BookedDays <=4 and BookedDays>=0) ,'Exclude',

               if( DayOfWeek = "Fri" and (BookedDays<=4 and BookedDays >=0) ,'',

                   if (DayOfWeek = "Sat" and (BookedDays <=4 and BookedDays >=0) ,'Exclude',                 

if( DayOfWeek= "Sun" and (BookedDays <=4 and BookedDays >=0) , 'Exclude'

                  , 'Include'

)))))))

Hope it helps

[#5]

This is close, but its Including everything except 0 values..

I.E. my BookedDays value = -20 and it is setting that to Include when it should be an exclude since I only want values from 0-4 (>=0 to <=4) as its in the if statement..

Also noticing that qlikview is very picky with the datetime and subtracting the 2 fields... an example:

bk_audit_datetime = 7/13/2012 9:31:54 AM

cr_prdate              = 7/17/2012 12:00:00 AM

subtracting them gives me 3, when it should be 4.. its taking the time in to consideration and I only need it to work from date itself

is there a better way in qlikview to evaluate the statement.. like with an array [0-4] or something?

Thanks so far..extremely helpful.

I had some classes through work for the graphical side, but it was horrible for explaining the ETL and scripting/formulas (some formulas at least)

[MayilVahanan]

Hi,

     Do you want to exclude -20 values, and you want values from 0 to 4 only.. Am i correct, if so, remove the include part from if condition..

Interval([bk_audit_datetime] - [cr_prdate],'D') as BookedDays 

     WeekDay([cr_prdate]) as DayOfWeek

if (DayOfWeek= "Mon" and (BookedDays <=4 and BookedDays >=0) ,'Exclude', 

                   if (DayOfWeek = "Tue and (BookedDays <=4 and BookedDays >=0) ,'Exclude',

                   if (DayOfWeek = "Wed" and (BookedDays <=4 and BookedDays >=0) ,'Exclude',

                if (DayOfWeek = "Thur" and (BookedDays <=4 and BookedDays>=0) ,'Exclude',

               if( DayOfWeek = "Fri" and (BookedDays<=4 and BookedDays >=0) ,'',

                   if (DayOfWeek = "Sat" and (BookedDays <=4 and BookedDays >=0) ,'Exclude',                 

if( DayOfWeek= "Sun" and (BookedDays <=4 and BookedDays >=0) , 'Exclude'                 

)))))))

qlikview is very picky with the datetime and subtracting the 2 fields... an example: 

bk_audit_datetime = 7/13/2012 9:31:54 AM

cr_prdate              = 7/17/2012 12:00:00 AM

subtracting them gives me 3,

It gives 4 when

bk_audit_datetime = 7/13/2012 12:00:00 AM

cr_prdate              = 7/17/2012 12:00:00 AM

Hope it helps

[#7]

I'll give that a shot, but as for the datetime fields.. how can I force a "real time" (the 9:31:54 AM) to become 12:00:00 AM?  The audit datetime field is ALWAYS 12:00:00 AM.

So basically I'd have to exclude the time part alltogether or force the cr_prdate field to become 12:00:00 AM so it will do a straight comparison

The overall goal is to pull out any Weekday where the difference between the audit time and cr_prdate less than 0 or greater than 4.  All values of 0, 1, 2,3,4 should be "ignored" or flagged to ignore in a selection field

[MayilVahanan]

Hi,

     Try like this,

     =Daystart ( '2006-01-25 16:45' ) returns '2006-01-25 00:00:00'

     Hope it helps