Trouble with mapping tables

datanibbler — Fri, 03 Aug 2018 10:58:40 GMT

Hi,

I have the following issue:

I am working with data coming from Excel files which has to be appended to data coming from SAP.

Now, the customer_numbers we have from SAP are usually filled up to be 10 characters long - filled up with 0s.

Okay - so I have to basically do the same with the data from Excel, easy enough so far.

The issue is, in some records from Excel there is no customer_number - I aggregate those to something like "2200\IT\Ext" - those would be all the records with no cust_no in the Bukrs 2200, the customer being in italy and being external. As you may have noticed, this has 11 characters, so filling up to 10 won't work.

So I came up with >> TRIM(Right(('0000' & [cust_no]), 11)) << - this would cut out 11 characters from the right and in case there is a regular cust_no, there would be a blank to the right which I cut off with TRIM().

Should work, no? Well, it doesn't - in some cases I still get an 11-character-number with five 0s to the left - how can that happen? There is no 0 to the right, only to the left - what went wrong there and how could I mend it?

Can somebody shed some light on this?

Thanks a lot!

Best regards,

DataNibbler

Re: Trouble with mapping tables

datanibbler — Fri, 03 Aug 2018 11:38:38 GMT

Okay,

I have found a way around it: I just cut off 11 characters and if the first one of those is a number as in "2200\IT\Ext", then I leave it so, otherwise - as in "0000240511" - I cut off one more.

That solves this.

topic Trouble with mapping tables in QlikView

Trouble with mapping tables

Re: Trouble with mapping tables