topic Re: How can I write an If statement for a Drop Field statement like this? in QlikView

How can I write an If statement for a Drop Field statement like this?

jblomqvist — Thu, 19 May 2016 10:33:59 GMT

Hi all,

I am trying to write a query like below:

TableA:

LOAD

floor(RAND()*17)+10 as Test,

1 as Counter

AutoGenerate 23;

If($(vDropField)=1, Drop Field Counter, False);

Basically I want to drop the field is the vDropField variable contains 1, else don't drop the field.

It doesn't work when I write it. Any idea how to do this please?

Re: How can I write an If statement for a Drop Field statement like this?

sunny_talwar — Thu, 19 May 2016 10:35:19 GMT

May be like this:

TableA:

LOAD Floor(Rand() * 17) + 10 as Test,

1 as Counter

AutoGenerate 23;

If $(vDropField) = 1 then

DROP Field Counter, False;

ENDIF

Re: How can I write an If statement for a Drop Field statement like this?

swuehl — Thu, 19 May 2016 10:38:22 GMT

You can keep to the example in the HELP:

if $(vDropField) =1 then; drop field Counter; end if;

edit:this should also work

if vDropField =1 then; drop field Counter; end if;

Re: How can I write an If statement for a Drop Field statement like this?

jblomqvist — Thu, 19 May 2016 10:42:38 GMT

Hi Sunny,

When I try it:

TableA:

LOAD

floor(RAND()*17)+10 as Test,

1 as Counter

AutoGenerate 23;

If $(vDropField)= 1 then

DROP Field Counter;

ENDIF;

I get the following error message:

Did I miss something?

I have created a Input Box in the UI called vDropField which has value of 1 to test it.

Re: How can I write an If statement for a Drop Field statement like this?

sunny_talwar — Thu, 19 May 2016 10:44:19 GMT

Can you add a Trace statement for your variable?

TRACE $(vDropField);

Exit Script;

Add this at the top of your script just to check if $(vDropField) is showing one or not.

Re: How can I write an If statement for a Drop Field statement like this?

Kushal_Chawda — Thu, 19 May 2016 10:48:07 GMT

try

If $(vDropField)=1 then

Drop Field Counter;

else TRACE "False"

ENDIF

Re: How can I write an If statement for a Drop Field statement like this?

jblomqvist — Thu, 19 May 2016 10:54:51 GMT

Hi Sunny,

It doesn't come through in the script when I check the Variables tab.

Re: How can I write an If statement for a Drop Field statement like this?

sunny_talwar — Thu, 19 May 2016 10:57:00 GMT

Can you check variable overview to see if you can see 1? Also can you make sure you have not mis-spelled or made a typo with the upper case lower case?

Re: How can I write an If statement for a Drop Field statement like this?

jblomqvist — Thu, 19 May 2016 10:58:14 GMT

Hi Swuehl,

The first code doesn't run and throws the same error as reply to Sunny above.

And second code runs but I still have the Counter field available even though vDropField variable carries 1.

Re: How can I write an If statement for a Drop Field statement like this?

swuehl — Thu, 19 May 2016 10:58:43 GMT

Where do you check this variable tab?

You should set the vDropField variable to 1 (and not to an expression like =If( A= B, 1) ) and check the correct case sensitiv spelling in all places.

For example, in the script:

Set vDropField = 1;

...

If vDropField = 1 ......

Re: How can I write an If statement for a Drop Field statement like this?

jblomqvist — Thu, 19 May 2016 11:05:28 GMT

Hi Sunny,

I seem to have solved it.

Strange behaviour initially, I had an InputBox with the variable value but it gave me the above error.

So then I went to Variable Overview and assigned a 1 to the variable and reloaded. And it ran fine and dropped the field.

Re: How can I write an If statement for a Drop Field statement like this?

sunny_talwar — Thu, 19 May 2016 11:09:06 GMT

That is strange, I would have expected inputbox to have worked the same way as variable view. Are you sure you were using the right variable in the inputbox? Can you check making changes to the value (may be assign 2) through the input box and see if that changes the value in variable overview? Trying to understand where the disconnect is.

Re: How can I write an If statement for a Drop Field statement like this?

swuehl — Thu, 19 May 2016 11:11:03 GMT

And have you actually confirmed the input value in your input box by either clicking another object or pressing Enter before starting the reload?

Re: How can I write an If statement for a Drop Field statement like this?

jagan — Thu, 19 May 2016 11:11:14 GMT

Hi,

Try like this

LET vDropField = 1; //or replace with your expression

TableA:

LOAD Floor(Rand() * 17) + 10 as Test,

1 as Counter

AutoGenerate 23;

If vDropField = 1 then

DROP Field Counter;

ENDIF

Re: How can I write an If statement for a Drop Field statement like this?

sunny_talwar — Thu, 19 May 2016 11:12:41 GMT

Seems to be working in the attached file

Re: How can I write an If statement for a Drop Field statement like this?

jblomqvist — Thu, 19 May 2016 11:13:12 GMT

Hi guys,

What I want to do is get the script to load the Counter field if vDropField does not evaluate to 1.

So far this code works if it evaluates to 1:

TableA:

LOAD

floor(RAND()*17)+10 as Test,

1 as Counter

AutoGenerate 23;

If $(vDropField) = 1 then

DROP Field Counter;

ENDIF

How can I put an ELSE in to load the Counter field if vDropField does not evaluate to 1?

In essence what I am trying to do is allow the user to say load the field or not.

Re: How can I write an If statement for a Drop Field statement like this?

swuehl — Thu, 19 May 2016 11:17:03 GMT

Let vCounterLoad = If( $(vDropField) = 1,'',', 1 as Counter');

TableA:

LOAD

floor(RAND()*17)+10 as Test

$(vCounterLoad)

AutoGenerate 23;

Re: How can I write an If statement for a Drop Field statement like this?

sunny_talwar — Thu, 19 May 2016 11:17:22 GMT

May be this:

TableA:

LOAD

floor(RAND()*17)+10 as Test,

1 as Counter

AutoGenerate 23;

If $(vDropField) <> 1 then

DROP Field Counter;

ENDIF

Re: How can I write an If statement for a Drop Field statement like this?

Kushal_Chawda — Thu, 19 May 2016 11:22:43 GMT

another solution

TableA:

LOAD

floor(RAND()*17)+10 as Test,

1 as Counter

AutoGenerate 23;

If $(vDropField) = 1 then

DROP Field Counter;

ELSE

Drop table TableA;

TableA:

LOAD

floor(RAND()*17)+10 as Test,

1 as Counter

AutoGenerate 23;

ENDIF

Re: How can I write an If statement for a Drop Field statement like this?

jagan — Thu, 19 May 2016 11:24:29 GMT

Hi,

Try like this for else

TableA:

LOAD

floor(RAND()*17)+10 as Test,

1 as Counter

AutoGenerate 23;

If $(vDropField) = 1 then

DROP Field Counter;

ELSE

// Else statements goes here

ENDIF