Splitting one field into a number of fields

[jacqueshol]

Hi All,

Heads-up!  I am new to all of this, so patience with me will be appreciated.

I have recently started looking at bringing in Accpac data from SQL into QlikView.  I have run into a bit of a problem that I can't get my head around. I have a ACCTID field that has the following format:  000000AA00000. E.g. one of the 1000's of values for ACCTID is 001140AB91004.  This field's composition can be explained as follows:

• The first four 0's indicate the account number (positions 1-4 in the field) - 0011 in the example above (Revenue)
• The next two 0's indicated the region (positions 5-6) - 40 in the example above (Coastal Region)
• The next two digits (AA) indicate the depot (positions 7-8) - AB in the example above (Alberton Depot)
• The next three 0'2 indicate the customer (positions 9-11) - 910 in the example above (Shell Customer)
• The last two 0'2 indicate the division (positions 12-13) - 04 in the example above (Fuel Division)

I want to be able to extract each of these five "subfields" as separate list boxes to be able to filter on.  In addition I need to be able to combine the 3rd and 4th set of "subfields" as one field being AA000 (AB910) in the example above).

Solutions I've tried

1. I had a look at using the Subfield function, but since I don't have any delimiter, I can't, or rather don't know , how to use it correctly.
2. I have also tried creating list boxes with expressions. E.g.
   • To get to Depot and Customer, I used:  =mid(ACCTID,7,5) which extracted AB910 (which is what I need).
   • To get the AccNo, I used: =left(ACCTID,4) which gave me 0011 (also what I need).
   • However, when I start filtering on these, it doesn't seem to work.  E.g. I select AB910 and then 0010, it discards the AB910 values. Vice versa.  There are values for 001140AB91004 though. If I select the original value of 001140AB91004, it filters perfectly, just not when I try and do it separately.

Thank you all for assisting.  I am looking forward to your feedback.

Take care,

Jacques

[jacqueshol]

I've just noticed that although I have a list box for each of the five values mentioned above, the field name remains ACCTID.  No wonder values are discarded when I select multiple selections.  I suppose I need to rename the new fields to appropriate names.  Just don't know how to do it.  For example, I would like to call the AB910 as "Cost_Centre" in my example above.  Any suggestions?

[jacqueshol · Accepted solution]

Hi All,

Strange how nicely things work out once you apply your mind.  I managed to solve my problem.  Just added this to my script load.

LOAD *,

  left(ACCTID,4) as ACCTNO,

  mid(ACCTID,5,2) as REGION,

  mid(ACCTID,7,2) as DEPOT,

  mid(ACCTID,9,3) as CUSTOMER,

  mid(ACCTID,7,5) as SCHEDULE_BOARD,

  right(ACCTID,2) as DIVISION;

SQL SELECT *

FROM "AP_TSFG_DAT".dbo.GLAFS;

SQL SELECT *

FROM "AP_TSFG_DAT".dbo.GLAMF;