Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
vardhancse
Valued Contributor III

Alternative to nested if

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

 

Labels (4)
2 Solutions

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Alternative to nested if

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.


talk is cheap, supply exceeds demand
MVP & Luminary
MVP & Luminary

Re: Alternative to nested if

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

7 Replies
MVP & Luminary
MVP & Luminary

Re: Alternative to nested if

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?

talk is cheap, supply exceeds demand
vardhancse
Valued Contributor III

Re: Alternative to nested if

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
Valued Contributor III

Re: Alternative to nested if

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

MVP & Luminary
MVP & Luminary

Re: Alternative to nested if

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.


talk is cheap, supply exceeds demand
vardhancse
Valued Contributor III

Re: Alternative to nested if

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.
MVP & Luminary
MVP & Luminary

Re: Alternative to nested if

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
Valued Contributor III

Re: Alternative to nested if

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 🙂