Solved: if statement in script load - Qlik Community

Report Inappropriate Content · ‎2011-05-06

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

ToniKautto · ‎2011-05-06

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

View solution in original post

Anonymous · ‎2011-05-06

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

Report Inappropriate Content · ‎2011-05-06

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

ToniKautto · ‎2011-05-06

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]

Report Inappropriate Content · ‎2011-05-06

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 ?

ToniKautto · ‎2011-05-06

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]

Report Inappropriate Content · ‎2011-05-06

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

Report Inappropriate Content · ‎2011-05-06

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

ToniKautto · ‎2011-05-06

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.

Anonymous · ‎2011-05-06

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

Related Topics