7 Replies Latest reply: Jul 18, 2012 10:00 AM by mayilvahanan ramasamy RSS

    Turning formulas/running total fields from crystal rpts to qlikview

      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!

        • Re: Turning formulas/running total fields from crystal rpts to qlikview
          mayilvahanan ramasamy

          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

            • Re: Turning formulas/running total fields from crystal rpts to qlikview

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

                • Re: Turning formulas/running total fields from crystal rpts to qlikview
                  mayilvahanan ramasamy

                  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

                    • Re: Turning formulas/running total fields from crystal rpts to qlikview

                      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)

                        • Re: Turning formulas/running total fields from crystal rpts to qlikview
                          mayilvahanan ramasamy

                          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