topic Re: How to get proper formula in the QV Script? in QlikView

How to get proper formula in the QV Script?

Wed, 29 Oct 2014 12:11:06 GMT

Dear All,

I need to redefine one of my QV dimensions, using other dimensions loaded. The simplest way would be to do this directly in the load script, however being able to get what I want in excel (attached) by writting appropriate formula, I am not able to mirror this in QV script.

Can anyone help me to convert the excel loginc into the QV script logic?

thanks a lot in advance

Re: How to get proper formula in the QV Script?

jyothish8807 — Wed, 29 Oct 2014 12:20:13 GMT

Hi Draszor,

Try something like this:

if(Product_Type='Finished good',substring(Sub_Product_Name,'/',1)&'-'& Size_Name,Sub_Product_Name)

Regards

Re: How to get proper formula in the QV Script?

Wed, 29 Oct 2014 12:28:07 GMT

thanks a lot, but I need as well the part for non Finished goods...

The formula basically says that:

If Product Type = Finished good and sub product name contains "/", then take the text until the "/" sign
Otherise, then take the text until the " " sign in Sub_Product_Name or if " " sign does not exist, take the whole string from Sub_Product_Name
add the Size_Name for Finished goods

Re: How to get proper formula in the QV Script?

Wed, 29 Oct 2014 12:31:56 GMT

Probably need to nest that if for all that, but definitely doable in QV

Re: How to get proper formula in the QV Script?

jyothish8807 — Wed, 29 Oct 2014 13:18:52 GMT

Hi ,

Try this:

if(Product_Type='Finished good' and wildMatch(Sub_Product_Name,'*/*')>0,substring(Sub_Product_Name,'/',1)&'-'& Size_Name,if(Product_Type='Finished good' and wildMatch(Sub_Product_Name,'*/*')<0,substring (Sub_Product_Name,' ',1) &'-'& Size_Name),Sub_Product_Name))

Regards

Re: How to get proper formula in the QV Script?

Wed, 29 Oct 2014 15:15:31 GMT

the Substring(field,'/',1) does not work. I tied to set it as separate variable and it does not work as well - the synthax is wrong...

From your example i conclude that the synthax you meant was:

substring(field, end of the string expression,start at), returning for "KRToo/oodd' the"KTR oo" one.

I could not find in any help materials that such synthax exists for substring expression.

Re: How to get proper formula in the QV Script?

jyothish8807 — Wed, 29 Oct 2014 16:26:19 GMT

Hi,

I am really sorry got confused with that function .

replace it with:

if(Product_Type='Finished good' and wildMatch(Sub_Product_Name,'*/*')>0,subfield(Sub_Product_Name,'/',1)&'-'& Size_Name,if(Product_Type='Finished good' and wildMatch(Sub_Product_Name,'*/*')<0,subfield (Sub_Product_Name,' ',1) &'-'& Size_Name),Sub_Product_Name))

Regards

Re: How to get proper formula in the QV Script?

Thu, 30 Oct 2014 08:11:40 GMT

thanks a lot KC,

now thi worked - I needed to do very few editorial changes like:

Instead of wildMatch(Sub_Product_Name,'*/*')<0

I used wildMatch(Sub_Product_Name,'*/*')=0

or I added the proper part of the formula for non Finished goods, but your code was 98% ready.

Very helpful, very quick, I am very glad

thanks

Regards

Rob

Re: How to get proper formula in the QV Script?

jyothish8807 — Thu, 30 Oct 2014 12:49:30 GMT

You are welcome Rob Actually i didnt try that syntex in app, i just wrote that so coudnt track my mistakes

Regards