topic Re: Problem with load script in QlikView

Problem with load script

Thu, 16 Jul 2015 11:42:54 GMT

Hello

Me again, i am not comming right with this:

if([Sale Finalized]='Yes' , if(round(("Return to Grower (GROSS)" - "Return to Grower X"),0.01) < '-0.01' OR
round(("Return to Grower (GROSS)" - "Return to Grower X"),0.01)>'0.01', 'Difference', 'Reconciling'),
if([Sale Finalized]='No' ,
if("SubSale Deal Type Text" = 'Fixed', 'Fixed',
if("SubSale Deal Type Text" = 'Consignment' and "Sale Deal Type Text" = 'Consignment' , 'Consignment'),
if("SubSale Deal Type Text" = 'Consignment' and "Sale Deal Type Text" = 'Fixed',
if(round(("Return to Grower (GROSS)" - "Return to Grower X"),0.01) < '-0.01' OR
round(("Return to Grower (GROSS)" - "Return to Grower X"),0.01)>'0.01', 'Difference', 'Reconciling'),
if("SubSale Deal Type Text" = 'Consignment' and "Sale Deal Type Text" = 'MGP', round(("Return to Grower (GROSS)" - "Return to Grower X"),0.01) < '-0.01' OR
round(("Return to Grower (GROSS)" - "Return to Grower X"),0.01)>'0.01', 'Difference', 'Reconciling'))))) as "Variance Report"

What am i not seeing?

Re: Problem with load script

MK_QSL — Thu, 16 Jul 2015 12:00:05 GMT

Your If Condition Mismatching !

Look into below

if("SubSale Deal Type Text" = 'Consignment' and "Sale Deal Type Text" = 'MGP', round(("Return to Grower (GROSS)" - "Return to Grower X"),0.01) < '-0.01' OR
round(("Return to Grower (GROSS)" - "Return to Grower X"),0.01)>'0.01', 'Difference', 'Reconciling')))))

IF(something then round(something) or Round(something)) this is wrong

Can you provide your full logic, I will create full if statement for you.

Re: Problem with load script

avinashelite — Thu, 16 Jul 2015 12:00:41 GMT

I didn't get whats the issue is can you please elaborate a bit more on what is happening and what your try to achieve

Re: Problem with load script

Kushal_Chawda — Thu, 16 Jul 2015 12:04:43 GMT

if([Sale Finalized]='Yes' , if(round(("Return to Grower (GROSS)" - "Return to Grower X"),0.01) < '-0.01' OR

round(("Return to Grower (GROSS)" - "Return to Grower X"),0.01)>'0.01', 'Difference', 'Reconciling',

if([Sale Finalized]='No' ,

if("SubSale Deal Type Text" = 'Fixed', 'Fixed',

if("SubSale Deal Type Text" = 'Consignment' and "Sale Deal Type Text" = 'Consignment' , 'Consignment',

if("SubSale Deal Type Text" = 'Consignment' and "Sale Deal Type Text" = 'Fixed',

if(round(("Return to Grower (GROSS)" - "Return to Grower X"),0.01) < '-0.01' OR

round(("Return to Grower (GROSS)" - "Return to Grower X"),0.01)>'0.01', 'Difference', 'Reconciling',

if("SubSale Deal Type Text" = 'Consignment' and "Sale Deal Type Text" = 'MGP', round(("Return to Grower (GROSS)" - "Return to Grower X"),0.01) < '-0.01' OR

round(("Return to Grower (GROSS)" - "Return to Grower X"),0.01)>'0.01', 'Difference', 'Reconciling')))))))) as "Variance Report"

Re: Problem with load script

Thu, 16 Jul 2015 12:09:23 GMT

mmm, okay, byt the as "Variance report" is still not blue, meaning the code is not correct yet

Re: Problem with load script

Thu, 16 Jul 2015 12:10:31 GMT

what changed?

Re: Problem with load script

MK_QSL — Thu, 16 Jul 2015 12:11:15 GMT

Can you provide some information on your If statement.

Something like

Sale Finalized = 'Yes' then

Sales Finalized = 'No' then

etc etc

Re: Problem with load script

sasiparupudi1 — Thu, 16 Jul 2015 13:20:23 GMT

Please try

if([Sale Finalized]='Yes',

if(Round(([Return to Grower (GROSS)] - [Return to Grower X]),0.01) < '-0.01' OR round(([Return to Grower (GROSS)] - [Return to Grower X]),0.01)>'0.01', 'Difference', 'Reconciling')

if([Sale Finalized]='No' ,

if([SubSale Deal Type Text] = 'Fixed', 'Fixed',

if([SubSale Deal Type Text] = 'Consignment' and [Sale Deal Type Text] = 'Consignment' , 'Consignment',

if([SubSale Deal Type Text] = 'Consignment' and [Sale Deal Type Text] = 'Fixed',

if(round(([Return to Grower (GROSS)] - [Return to Grower X]),0.01) < '-0.01' OR

round(([Return to Grower (GROSS)] - [Return to Grower X]),0.01)>'0.01', 'Difference', 'Reconciling',

if([SubSale Deal Type Text] = 'Consignment' and [Sale Deal Type Text] = 'MGP', round(([Return to Grower (GROSS)] - [Return to Grower X]),0.01) < '-0.01' OR

round(([Return to Grower (GROSS)] - [Return to Grower X]),0.01)>'0.01', 'Difference', 'Reconciling'))))))) as "Variance Report"

Re: Problem with load script

Thu, 16 Jul 2015 13:45:51 GMT

The “as” part does not turn blue

Re: Problem with load script

sasiparupudi1 — Thu, 16 Jul 2015 13:51:00 GMT

Please share your qvw so that we could troubleshoot it for you

Re: Problem with load script

Thu, 16 Jul 2015 14:20:55 GMT

okay, what detail do you require, as i don't know who else to give the statement thru. I just think there is a issue with :

the Reconciling and Consigment falses are not allowing the code to run through

Re: Problem with load script

Thu, 16 Jul 2015 14:21:29 GMT

I could give you the complete code?

Re: Problem with load script

MK_QSL — Thu, 16 Jul 2015 14:22:36 GMT

We don't need Code.. We need to understand the logic behind your nested if sentence !

Re: Problem with load script

kuczynska — Thu, 16 Jul 2015 14:27:54 GMT

As the rest of the guys mentioned that before - I'm not sure what is that you're trying to achieve here. I don't think your IF statement is nested properly - the best way to check it is probably to draw a block diagram on the paper and try some code again. Manish mentioned above:

IF(something then round(something) or Round(something)) this is wrong

and I think he is right. One of the ways to achieve the result of logical ORs and ANDs using the IF statement would be probably to nest more and more IF functions into your code. I think you can also think about using the FABS() function to get the absolute value for the below calculation:

FABS(Round(([Return to Grower (GROSS)] - [Return to Grower X]),0.01)) > 0.01

which allow you to simplify at least one part of the IF statement. Try to replace them all using fabs().

You are combining few dimensions at once - maybe it would be better to split it into smaller chunks, create separate flags in your load script and than try to organize some additional mapping using the MATCH() function in preceding load?

Remember the basics - IF(expression, then, else )

From my understanding the code below:

seems to be a full IF statement straight away - where you have:

expression >>> [Sale Finalized]='Yes'

expression >>> (nested IF) IF(FABS(Round(([Return to Grower (GROSS)] - [Return to Grower X]),0.01)) > 0.01)

then >>> 'Difference'

else >>> 'Reconciling'

(end)

When nesting IF statements try to always start from the biggest, more "global" conditions, going down and down into to lowest level of detail.

Good luck!

Re: Problem with load script

Thu, 16 Jul 2015 14:49:42 GMT

okay, so we are trying it to say that is [Sale Finalized]='Yes' , then have a look at the sum in the call and if the values are in line as set in the code then display 'Difference', else display 'Reconciling'.

but

if [Sale Finalized]='No' , and SubSale Deal Type Text" = 'Fixed the display the outcome as Fixed...right track?

Re: Problem with load script

MK_QSL — Thu, 16 Jul 2015 14:56:35 GMT

if([Sale Finalized]='Yes' ,

if(round(("Return to Grower (GROSS)" - "Return to Grower X"),0.01) < '-0.01' OR
round(("Return to Grower (GROSS)" - "Return to Grower X"),0.01)>'0.01', 'Difference', 'Reconciling'),

Yes portion seems to be OK

if([Sale Finalized]='No' ,
     if("SubSale Deal Type Text" = 'Fixed', 'Fixed',
     if("SubSale Deal Type Text" = 'Consignment' and "Sale Deal Type Text" = 'Consignment' , 'Consignment'),
     if("SubSale Deal Type Text" = 'Consignment' and "Sale Deal Type Text" = 'Fixed',

No portion is also OK

if(round(("Return to Grower (GROSS)" - "Return to Grower X"),0.01) < '-0.01' OR

round(("Return to Grower (GROSS)" - "Return to Grower X"),0.01)>'0.01', 'Difference', 'Reconciling'),

Again in No portion you are checking the same as yes portion

But what about below ????? This is very difficult to understand.

Re: Problem with load script

sasiparupudi1 — Fri, 17 Jul 2015 12:04:19 GMT

Please try

if([Sale Finalized]='Yes' ,

if(round([Return to Grower (GROSS)]-[Return to Grower X] ,0.01) < '-0.01' OR round([Return to Grower (GROSS)] - [Return to Grower X] ,0.01)>'0.01', 'Difference', 'Reconciling'),

if([Sale Finalized]='No' ,

if([SubSale Deal Type Text] = 'Fixed', 'Fixed',

if([SubSale Deal Type Text] = 'Consignment' and "Sale Deal Type Text" = 'Consignment' , 'Consignment'),

if([SubSale Deal Type Text] = 'Consignment' and "Sale Deal Type Text" = 'Fixed',

if(round([Return to Grower (GROSS)] - [Return to Grower X],0.01) < '-0.01' OR

round([Return to Grower (GROSS)] - [Return to Grower X],0.01)>'0.01', 'Difference', 'Reconciling'),

if([SubSale Deal Type Text] = 'Consignment' and [Sale Deal Type Text] = 'MGP', if(round([Return to Grower (GROSS)] - [Return to Grower X],0.01) < '-0.01' OR

round([Return to Grower (GROSS)] - [Return to Grower X],0.01)>'0.01', 'Difference', 'Reconciling')))))) as "Variance Report"

I think you are missing an if

round(("Return to Grower (GROSS)" - "Return to Grower X"),0.01) < '-0.01' OR
round(("Return to Grower (GROSS)" - "Return to Grower X"),0.01)>'0.01', 'Difference', 'Reconciling'))))) as "Variance Report"

hope this helps