topic if statement in script load in QlikView

if statement in script load

Fri, 06 May 2011 08:11:47 GMT

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

Anonymous — Fri, 06 May 2011 08:18:42 GMT

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

Fri, 06 May 2011 08:40:16 GMT

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

ToniKautto — Fri, 06 May 2011 08:45:11 GMT

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

Fri, 06 May 2011 08:52:00 GMT

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

ToniKautto — Fri, 06 May 2011 08:56:00 GMT

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

Fri, 06 May 2011 09:03:22 GMT

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

Fri, 06 May 2011 09:16:01 GMT

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

ToniKautto — Fri, 06 May 2011 09:22:02 GMT

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

Anonymous — Fri, 06 May 2011 09:34:29 GMT

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

Fri, 06 May 2011 09:35:33 GMT

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

if statement in script load

ToniKautto — Fri, 06 May 2011 11:43:15 GMT

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

if statement in script load

Fri, 06 May 2011 12:26:15 GMT

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

if statement in script load

Fri, 06 May 2011 15:17:38 GMT

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

if statement in script load

ToniKautto — Sat, 07 May 2011 08:26:10 GMT

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