14 Replies Latest reply: May 7, 2011 4:26 AM by Toni Kautto RSS

    if statement in script load

      Hi Guys,

       

      Hope you can help -

       

      I have an Excel sheet loaded into Qlikview , where there are 2 columns [Structured] and [Unstructured].

       

      I wish to add another column manually within the script called [Time to complete], where

       

      [Structured] is populated I wish for [Time to complete] to be '1' and where [Unstructured] is populated I wish for [Time to complete] to be '8'.

       

      I have tried a few variations of the if statement but I'm continously getting an error on the Reload, can anyone advise me please ?

       

      Or perhaps there is a better solution such as mapping ??

       

      Thanks

        • Re: if statement in script load
          Artjoms Tukums

          Hi,

          Have you tried something like this:

           

          If(Len(Trim([Structured]))>0,1,If(Len(Trim([Unstructured]))>0,8)) as [Time to complete]

           

          Rgds,
          AT

            • if statement in script load

              Hi Artjoms,

               

              the above is defaulting at 1 , so when  I have both columns populated it will apply time to complete as 1 ( as its the first option in the formula) -- ( I think!!)

               

              Maybe I can explain my issue a little better,

               

              Ideally I would like when Unstructured is populated , Time to complete is 8 , when Structured is populated , Time to complete is 1, so when BOTH are populated it assigns Time to complete accordingly. Would some sort of mapping be more suitable ? Or is there a way around the above

               

              Thanks so much for your help

              Anne

                • if statement in script load
                  Toni Kautto

                  You mean the result should 0, 1, 8 or 9?

                   

                  If(Len(Trim([Structured]))>0,1, 0) + If(Len(Trim([Unstructured]))>0,8, 0)) as [Time to complete]

                    • if statement in script load

                      Hi tko,

                       

                      Yeah thats kind of the issue, I need like a duplicate result , where Struct is populated 1 , Unstruct 8 or if BOTH assign Time to Complete as those that are in Struct 1 those in Unstruct 8.

                       

                      Ideally, if I could have 2 columns called Tme to complete and it would fill in 0 for whichever column not completed and Both results when BOTH are populated.

                       

                      Do you think its possible ?

                        • if statement in script load
                          Toni Kautto

                          My suggested statement above shoudl do that, or did I miss out on something?

                           

                          If(Len(Trim([Structured]))>0,1, 0) + If(Len(Trim([Unstructured]))>0,8, 0)) as [Time to complete]

                            • if statement in script load

                              Hi,

                              Can u elaborate your concern?

                              "Structured is populated" means what?

                              Please give a sample table created in Excel that you want from QV.

                              Cover maximum possible combinations of values possible so that accurate solution can be given.

                               

                              Thanks,

                              Happy Thoughts

                              • if statement in script load

                                Hi tko,

                                 

                                the formula is slightly different the second time posted, I've bolded the differences below -

                                 

                                If(Len(Trim([Structured]))>0,1, 0) + If(Len(Trim([Unstructured]))>0,8, 0)) as [Time to complete]

                                 

                                I reckon thats the part of the formula that makes it Zero if there is no data available in both columns.

                                 

                                However when I loaded it in I get the below Syntax error -

                                 

                                Syntax error, missing/misplaced FROM:

                                CONCATENATE (MasterTable)

                                LOAD 

                                     Date(Date) as Date,

                                     Month(Date(Date)) as Month,

                                     Week(Date(Date)) as Week,

                                     Year(Date(Date)) as Year,   

                                     [Flexcube Branch],

                                     [No. of Unstructured Rollovers],

                                     [No. of Structured Rollovers],

                                     [No. of Structured Closures],

                                     [No. of Unstructured Rollovers]+[No. of Structured Rollovers] as Number,

                                     [No. Manual Dockets],

                                     [Manual Dockets not processed on Day],

                                     If(Len(Trim([No. of Structured Rollovers]))>0,1, 0) + If(Len(Trim([No. of Unstructured Rollovers]))>0,8, 0)) as [Time to complete]

                                 

                                 

                                Can you see where I made a mistake ?

                                 

                                Thanks

                                  • if statement in script load
                                    Toni Kautto

                                    Exactly, if the string lengths are above 0 then 1 or 8 will be returned else 0. And then theses values will be added to each other and presented as the field  [Time to complete].

                                     

                                    In the script editor you should be able to see that the last ) is highlighted in red, meaning the corresponding ( is missing. In this case the last ) should not be there, so please remove it and you should be fine.

                                    • if statement in script load
                                      Artjoms Tukums

                                      remove last bracket:

                                      If(Len(Trim([No. of Structured Rollovers]))>0,1, 0) + If(Len(Trim([No. of Unstructured Rollovers]))>0,8, 0) as [Time to complete]

                                    • if statement in script load


                                      Hi Toni,

                                       

                                      Sorry, I see , I added a ')' when I pasted in to script, I've fixed the syntax error.

                                       

                                      This is giving a result of 9 for Time to complete when both columns in excel have data present. - I do not wish the Time to complete to be Summed ,

                                       

                                      Ravi, below is the loaded data.

                                       

                                      Excel 1.bmp

                                       

                                      In excel I would wish it to look like -

                                       

                                      Excel 2.bmp

                                       

                                      So for example, if I was to build a chart for the 7/3/11 Total time to complete would be; 353 -- being (38*8 +49*1)

                                       

                                      Is this possible ?

                                       

                                      Thanks

                                      Anne

                                        • if statement in script load
                                          Toni Kautto

                                          Yes, there is no end to the possibilities of what you can do.

                                           

                                          Looking at the script sample you provided I would suggest that you think over your data model to avoid having to change it to much later on. What fields should be calculated already on load, making them static, and which fileds to calculate in the application, making them dynamic.

                                           

                                          It is quite common to have a master calender table to link your dates to. That way you only have the date in the data table and the week, month, quarter details in the master calendar table. A selection on a quarter will then limit the data in the calendar table and thereby also limit the linked values in the data table.

                                           

                                          If you want to precalculate the data in script, and want to avoid repeating IF statements over and over, an eficient way can be to have a preceeding load as in the example below. The previously loaded table in such a scenario becomes the input for the following load. As in my example below if your iniital values might be empty it can be good to set them to zero, to make them numeric and suitbale for further calculations. In the following load you then know that the values are valid and can use them as such right away.

                                           

                                          Then you can add even a third load on top to use the fields for your final calculation. This gives a easy to follow script and thereby easy to debug or alter if required.

                                           

                                           

                                          LOAD

                                               *,

                                               [No. of Structured Rollovers]*[Time to complete Structured] +

                                               [No. of Unstructured Rollovers]*[Time to complete Unstructured] as TotalTime

                                               ;

                                          LOAD

                                                    *,

                                                    If([No. of Structured Rollovers]>0,1, 0) as [Time to complete Structured],

                                                    If([No. of Unstructured Rollovers]>0,8, 0) as [Time to complete Unstructured],

                                                    [No. of Unstructured Rollovers]+[No. of Structured Rollovers] as Number,

                                                    ;

                                          LOAD

                                               Date(Date) as Date,

                                               [Flexcube Branch],

                                               if(Len(Trim([No. of Unstructured Rollovers])>0), Num([No. of Unstructured Rollovers]), 0) as [No. of Unstructured Rollovers],

                                               if(Len(Trim([No. of Structured Rollovers])>0), Num([No. of Structured Rollovers]), 0) as [No. of Structured Rollovers],

                                               [No. of Structured Closures],

                                               [No. Manual Dockets],

                                               [Manual Dockets not processed on Day]

                                           

                                           

                                           

                                          As I said this just an example, you have to analyse your own scenario and see what fits best for your purposes. Hope it has helped in some way