topic Re: Problem in a LOAD statement in QlikView

Problem in a LOAD statement

datanibbler — Wed, 10 Jun 2015 13:33:13 GMT

Hi,

I am loading data from a large Excel sheet and I have a number of formulas and Applymap-commands in the LOAD.

Among them is one line like

>> IF (PACK_TIME1 > 0, 'Packzeit', IF(LEN(KeepChar(ComCode, 'LV'))>0, 'LV', 'Vol')) as Art_Kostenberechnung_syncreon, <<

=> If there is a positive packtime, I want the output to be 'Packzeit', the price is calculated using that.

=>=> If not, I want to check whether the ComCode contains the letters 'LV' and if yes, I want 'LV', otherwise I want 'Vol'.

Sounds pretty easy, doesn't it? Still, when I select the 0 in the field 'Packtime' and I select 'LV' in the field 'Art_Kostenberechnung_syncreon', I get a number of CommentCodes that don't contain the letters 'LV'.

Can anybody tell what's wrong there?

Thanks a lot!

Best regards,

DataNibbler

Re: Problem in a LOAD statement

giakoum — Wed, 10 Jun 2015 13:37:38 GMT

sample app?

Re: Problem in a LOAD statement

Gysbert_Wassenaar — Wed, 10 Jun 2015 13:41:15 GMT

Is that load statement the only one that creates a table with the Art_Kostenberechnung_syncreon field? Or does that field exist in another table too? And the same question for the Packtime (or is it PACK_TIME1?) field.

Re: Problem in a LOAD statement

Colin-Albert — Wed, 10 Jun 2015 13:42:32 GMT

Keepchar will remove all characters except L or V from ComCode, so if Comcode contains any text with a capital L or a Capital V, then the len expression will return a value greater than zero.

It may be better to use index(ComCode, 'LV')

Re: Problem in a LOAD statement

datanibbler — Wed, 10 Jun 2015 13:44:04 GMT

Hi,

those fields both exist only once each. The field_name is PACK_TIME1 in the original because there are two fields with packtimes in the report - internal and external.

Re: Problem in a LOAD statement

datanibbler — Wed, 10 Jun 2015 13:46:49 GMT

Hi Colin,

that is most probably it. I just took a quick look and it seems that all ComCodes that I do not actually want, but that do appear either contain an L or a V - not both, but only one. I want only those where there is the sequence 'LV' somewhere in the ComCode.

I'll see whether I can do better using INDEX().

Thanks a lot!