if statement in script load

[#1]

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

[#2]

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.

In excel I would wish it to look like -

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

[ToniKautto · Accepted solution]

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

[#4]

Toni,

Thanks so much, makes perfect sense , Going to try and make it happen now by editing script , ill let you know how I go,

Thanks for such a good response

Anne

[#5]

Hey Toni,

Sometimes the simple answers are the best !!! Took your suggestion and spilt out the load of the sheet, works perfectly would never have thought of it ,

Thanks you saved me alot of grey hair !!!
Anne

[ToniKautto]

Great to hear you got it to work, and that you have mastered one of QlikView many smart and easy ways to work!