topic Re: How to load my data the right way in Connectivity & Data Prep

How to load my data the right way

lucas3 — Wed, 29 Jun 2022 14:56:06 GMT

Hi all,

I am loading a bunch of data from an excel file into qliksense

I have a column named "Account Name" and another one named "Account Numbers Strings" which contains data of this form: "A|B|C|D|E|F|G"
where A, B, C, etc are valuable informations (actually accounts numbers).

In one cell, I have no fixed ammount of information, I can have just "A", "A|B|C" or "A|...|Z|AA|..|AZ|BA|........" with no fixed limit.

I am wondering how to load my data in such a way that I have only 2 fields: "Account Name" and "Account Number", where "Account Number" can contain only one Account Number

Is that feasible in QlikSense without touching my Excel file ?

Thank you

Re: How to load my data the right way

marcus_sommer — Wed, 29 Jun 2022 15:38:51 GMT

It could be done with subfield(), like:

load AccountName, subfield(AccountString, '|') as AccountNumber
from Excel;

- Marcus

Re: How to load my data the right way

lucas3 — Thu, 30 Jun 2022 08:55:03 GMT

It works thanks !
Other question, how to load a constant number field ?

I tried with Load 1 as "FieldName" but it does not work

Re: How to load my data the right way

marcus_sommer — Thu, 30 Jun 2022 10:14:31 GMT

The following counter-fields might be useful in your case:

load
recno() as RecNo, rowno() as RowNo, iterno() as IterNo,
AccountName, subfield(AccountString & '|', '|', iterno()) as AccountNumber
from Excel
while iterno() <= substringcount(AccountString, '|') + 1;

Subfield() is now specified with 3 parameters what disabled the loop-functionality of it and this feature is added per while-loop which enables the possibility to use iterno() as a counter.

- Marcus