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

[jblomqvist]

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?

[jblomqvist]

Hi Swuehl,

Nice suggestion.

When I run it I get the following error:

[swuehl]

Note that I removed the comma after Test in my sample.

edit: ... and that the comma is added to the  vCounterLoad variable in the else branch.

[Kushal_Chawda]

or

If $(vDropField) = 1 then

TableA:

LOAD

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

//1 as Counter

AutoGenerate 23;

ELSE

TableA:

LOAD

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

1 as Counter

AutoGenerate 23;

ENDIF

[jblomqvist]

Hi Swuehl,

I think it works now with 1 or 0 input values (see attached).

But when I try to modify your code with yes or no input value. E.g. Yes drop Counter field, No do not, the code does not work.

Any idea how can I get the attached to work like that please?

[swuehl]

What exactely is not working? You need to adjust the conditional statement in the script, of course:

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

[jblomqvist]

I see what you had to do which is put single quotes around the variable. I was missing that.

Any idea why sometimes a variable needs single quotes and other times it doesn't?

[swuehl]

In general, if you want to compare text literals, use single quotes around the literals.

[jblomqvist]

Hi guys,

I have managed to get the code working.

The next problem I have is getting the Applymap to work in a variable:

Customers_Map:

Mapping LOAD * INLINE [

    CustomerCode, CustomerName

    1, Customer1

    2, Customer2

    3, Customer3

];

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

LET vDropCustomerGroup = If( '$(vDropCounterField)' = 'Yes','',', ApplyMap('Customers_Map',CustomerCode, 'Not Found') as CustomerName');

LET

TableA:

LOAD

*,

ApplyMap('Customers_Map',CustomerCode, 'Not Found') as CustomerName1 //This works

$(vDropCustomerGroup);

LOAD

floor(RAND()*170)+10 as OrderID,

Floor(Rand() *11) as TRA_Transaction_Ref,

Floor(Rand() *15) as TRA_Sort_Code,

'Not clarified yet' as ENA_Sort_Description,

Floor(Rand() *5) as CustomerCode

$(vCounterLoad)

AutoGenerate $(vHowManyRows);

Drop field CustomerCode;

---------------

In the above, the codes in bold are where I think the problem is and I get the following error:

I am trying to apply the same logic in Let vCounterLoad for vDropCustomerGroup but it does not work.

I think the problem is with single quotes clash everywhere and I tried resolving it by putting that entire applymap code in another variable and doing an dollar expansion where you see the current applymap code but could not get it to work.

I have ensure the variable is created in the UI, made sure there are no typing errors with the variable name so that should not be an issue.

Any suggestions please?

[swuehl · Accepted solution]

Try

LET vDropCustomerGroup = If( '$(vDropCounterField)' = 'Yes','',', ApplyMap(''Customers_Map'',CustomerCode, ''Not Found'') as CustomerName');

Note the use of two single quotes as escape character within the ApplyMap()

[jblomqvist]

Thank you swuehl you are a genius