Alternative to nested if

[vardhancse]

Hi,

I have few nested if in my script while loading CSV data, however due to restrictions/limit of 99 trying to change if and use PIck() and wildmatch().

Facing one issues is like:

Pick(Wildmatch(sample,'ABC',BCD','DEF','HIJ'),'Sample1','Sample2','Sample3','Sample4') as testfield

for an instance lets assume we don't have  'BCD' in sample field/column

So now my script is getting failed error"field not found: "Sample2""

As an alternative used similar script:

Pick(Wildmatch(sample,'ABC',BCD','DEF','HIJ')+1,'not available','Sample1','Sample2','Sample3','Sample4') as testfield

but no luck, can any let me know alternative solution for the same

[Gysbert_Wassenaar]

I don't see why you would get a field not found error. In the example you posted all values are in single quotes and will be treated as literal strings, not field names. Are you sure you're not using double quotes in your actual script instead of the single quotes you posted here?

[vardhancse]

Hi Gysbert,

Sorry, I'm using filed names directly not the string with single quotes:

Pick(Wildmatch(sample,'ABC',BCD','DEF','HIJ'),"Sample1","Sample2","Sample3","Sample4"') as testfield

for an instance lets assume we don't have  'BCD' in sample field/column

So now my script is getting failed error"field not found: "Sample2""

As an alternative used similar script:

Pick(Wildmatch(sample,'ABC',BCD','DEF','HIJ')+1,'not available',"Sample1","Sample2","Sample3","Sample4") as testfield

**Please note Sample1, Sample2, Sample3, & Sample4 are field names.

[vardhancse]

Ideally my requirement is that:

1. Irrespective of data@csv files  availability in source/drive, it should load all available csv files (future some more additional files might come )

2. Apply some business logic's, generate calculated fields (Using pick and match function)

3. Generate charts on top of fields

applied below logic:

Temp:

Load 0 as dummy AutoGenerate 0;

concatenate(Temp)

Load *

From [..\load\*Sample*.csv]

(txt, utf8, embedded labels, delimiter is ';', no quotes); 

//in future anticipating to get more CSV files, structure is not same across all CSV files, but will have some common fields

Drop Field dummy;

Main:

Load 

Pick(Wildmatch(sample,'ABC',BCD','DEF','HIJ'),"Sample1","Sample2","Sample3","Sample4"') as testfield, //Calculated field

Field1,

Field2

Resident Temp;

Drop Table Temp;

similar to above mentioned calculated fields, we have more business rules, as its crossing >99 nested if, tried using Pick and Wildcard to achieve my expected output

Can please let me  know, any alternative solution for my requirements

[Gysbert_Wassenaar · Accepted solution]

Before you can use a field name in an expression you must ensure that field is already created. There's no way around that. If you have to you can create those fields the same way you create the dummy field in the empty Temp table at the start of the script you posted.

[vardhancse]

Thank you so much for your suggestion.

One clarification please, for time being few of the fields being mentioned in pick and wild match were not available in data source at the moment.

However anticipating to come from source in near future.

If at all we create in temp table, but what if it’s coming from source directly in source level

Can please let me know how can we able to manage that scenario

Load 0 as Sample1 AutoGenerate 0;
Load 0 as Sample2 AutoGenerate 0;

Once again thank you for your support and it’s very useful.

[marcus_sommer · Accepted solution]

The fields needs to be available in one table and not anywhere within the datamodel. This means you would rather need something like this:

table: Load 0 as Sample1, 0 as Sample2, 0 as Sample3, .......... AutoGenerate 0;

But I suggest to rethink your whole load-approach and the deducing datamodels. I assume that your files contain some kind of a crosstable and a transforming of it might make the required merging and/or associations much easier. To what is meant take a look here: The-Crosstable-Load

- Marcus

[vardhancse]

Hi Markus,

Thank you for your suggestion, added all additional fields in my temp table and it worked out.

I can not go with cross table, like post that my rows and columns will be swapped.

Once again thank you 🙂