topic Re: LOAD statement - complex IF-construct as a variable in QlikView

LOAD statement - complex IF-construct as a variable

datanibbler — Wed, 23 Jul 2014 12:07:31 GMT

Hi,

in one of our primary loading apps, I have found an IF-construct (to define the working areas) that runs over eight lines ... to make the whole thing more resource-friendly and, more important, to avoid the risk of introducing slight differences when using the same code in another place, I want to try to create a variable for this expression and store it in a qvs file.

So I tried as I normally do like

>> LET e_formula = ' ... '; <<

<=> That, however, did not work in this case. The formula of course contains a lot of blanks, so I tried enclosing the whole thing in square brackets like

>> LET e_formula = '[ ... ]'; <<

<=> but that didn't work, either. Moreover, there are a lot of text_strings which are enclosed in upper_quotes and it contains a lot of Wildmatch() functions with *.

Can anyone give me a hint how I could get this to work?

Thanks a lot!

Best regards,

DataNibbler

Re: LOAD statement - complex IF-construct as a variable

sujeetsingh — Wed, 23 Jul 2014 12:09:47 GMT

Try using trim() function of qlikview

Trim(Field)

In order to remove spaces

Re: LOAD statement - complex IF-construct as a variable

Wed, 23 Jul 2014 12:17:41 GMT

Hi DataNibbler,

I would put this code inside a .txt file and then simply call it everytime I would like to use it by using the include function.

Regards,
Marius

Re: LOAD statement - complex IF-construct as a variable

datanibbler — Wed, 23 Jul 2014 12:26:53 GMT

Hi Marius,

that's exactly what I want to do. Is there a way to do this other than to put it into a variable?

I have not been able to do that so far.

Also, I would like to simplify the formula a bit, that would be more resource-friendly and maybe then it would be easier to put it in a variable, too.

Right now, as I said, it is an If-construct running over like eight lines, with WildMatch() functions - so I think I could not use PICK(WILDMATCH()) instead - there are several possible texts to match in every line which correspond to one area.

I will post the code here. Maybe someone has an idea?

LOAD

....

if(WildMatch(PACK_DELIVERY_BIN,'*KT*','TIMEKT','AT*','WERTE RSML','XGTKT'),'KT',

if(WildMatch(PACK_DELIVERY_BIN,'56GTBO','56GTSI','56GFG','56GTGW'),'56',

if(WildMatch(PACK_DELIVERY_BIN,'*BT*','BTZW*','GT','GT1*','GT2','GT3','GT6','GT7','GTL*','GTR*','*FAST','*SLOW','GT ESD','GTXL','GT AIR'),'GT',

if(WildMatch(PACK_DELIVERY_BIN,'LV*','GLL*'),'LV',

if(WildMatch(PACK_DELIVERY_BIN,'GTH*','*GEFAH*','GELENKW','WERTEXGT'),'GTH',

if(WildMatch(PACK_DELIVERY_BIN,'56GETR*','R3GETR*','GETRIEBE*','56LV','R3L*'),'GETRIEBE',

if(WildMatch(PACK_DELIVERY_BIN,'GTB','EVGTB'),'GTB',

if(WildMatch(PACK_DELIVERY_BIN,'GTBO'),'GTBO',

if(WildMatch(PACK_DELIVERY_BIN,'GTXL*'),'GTXL',

if(WildMatch(PACK_DELIVERY_BIN,'GTSW'),'GTSW',

'Sonstige')))))))))) as Packplatz,

...

;

Re: LOAD statement - complex IF-construct as a variable

Wed, 23 Jul 2014 12:30:50 GMT

How does the data look for PACK_DELIVERY_BIN? Do you only have a certain set of them or are there millions of different variations.

If only a hand full then I will use a mapping table instead.

And this script must take some time to run with all those wildmatches?

Regards,
Marius

Re: LOAD statement - complex IF-construct as a variable

datanibbler — Wed, 23 Jul 2014 12:42:05 GMT

Hi Marius,

honestly I don't know how many variations of the text in >PACK_DELIVERY_BIN< there might be that point to one and the same area - I am not alltoo familiar with the database where this comes from.

I will ask my colleague. If there are no more than 10 or so, then of course it would be possible to make this into a mapping table. The problem is, there is no proper documentation of our database available, so there is always a bit of guesswork involved ...

Best regards,

DataNibbler

Re: LOAD statement - complex IF-construct as a variable

Wed, 23 Jul 2014 12:48:46 GMT

Sure thing. You can put that load script into a .txt file in the meantime and simply call it using the following in the mean time.

$(Include=abc.txt);

Regards,
Marius

Re: LOAD statement - complex IF-construct as a variable

datanibbler — Wed, 23 Jul 2014 13:14:49 GMT

Hi Markus,

well, unfortunately that is not the only field I need to load from that particular database_table, so it's not that easy. I'll have to try to join that field to the table - that way I could do as you suggest.