topic Re: Excell with some columns to deconcatenate (variable length sperated by a point) and others not in QlikView

Excell with some columns to deconcatenate (variable length sperated by a point) and others not

HWallays — Tue, 20 Nov 2018 08:44:20 GMT

I need to import a large excell file where one column holds a concatenated field ( 12 different fields) which are seperated by a point '.', the other excell columns are fine...

Here is some example of that concatenate fields

AC.1.TR.AUD.1000.5100.1341..........CREDIT

AC.1.TR.AUD.1000.5100.1411..........CREDIT

MG.1.TR.BGN.25015.5100.2601.T....56.....51010

MG.1.TR.BGN.25015.5100.2601.T....56.....LIVEDB

MG.1.TR.BGN.25015.5100.2611.20121118....59....51010

MG.1.TR.BGN.25015.5100.2611.20140422....59....51010

Is there anyway I can import this in an elegant way without having to work with if constructions and mid/lef/right ...

The problem is that they have not made each of the sub fields equally long

Just as an example in the first 2 rows the field 5th field is only 4 signs long , in the others 5

Thanks for your (appreciated) help

Re: Excell with some columns to deconcatenate (variable length sperated by a point) and others not

jyothish8807 — Tue, 20 Nov 2018 09:07:20 GMT

Hi Hwallays,

Try loading the column by setting '.' as delimiter.

https://help.qlik.com/en-US/qlikview/November2018/Subsystems/Client/Content/QV_QlikView/Scripting/ScriptSpecifiers/delimiter.htm

LOAD

[YourField]

from a.txt (utf8, txt, delimiter is '.' , embedded labels);

//This will split the fields into multiple fileds as per the delimiter, you can do it from the load transformation section as well.

Re: Excell with some columns to deconcatenate (variable length sperated by a point) and others not

marcus_sommer — Tue, 20 Nov 2018 09:48:24 GMT

You could use subfield() for it, for example:

...

subfield(YourField, '.', 1) as YourField1,

subfield(YourField, '.', 2) as YourField2

...

- Marcus

Re: Excell with some columns to deconcatenate (variable length sperated by a point) and others not

HWallays — Tue, 20 Nov 2018 10:23:58 GMT

Hai Marcus , that did works really well ...so many thanks on this one !

Here 's a view on the result

AC.1.TR.AUD.1000.5100.1211..........CREDIT	AC	1	TR	AUD	1000	5100	1211
AC.1.TR.AUD.1000.5100.1231..........CREDIT	AC	1	TR	AUD	1000	5100	1231
AC.1.TR.AUD.1000.5100.1341..........CREDIT	AC	1	TR	AUD	1000	5100	1341
AC.1.TR.AUD.1000.5100.1411..........CREDIT	AC	1	TR	AUD	1000	5100	1411
AC.1.TR.AUD.1000.5100.1412..........CREDIT	AC	1	TR	AUD	1000	5100	1412
MG.1.TR.BGN.25015.5100.2601.T....51.....51010	MG	1	TR	BGN	25015	5100	2601	T	51
MG.1.TR.BGN.25015.5100.2601.T....51.....LIVEDB	MG	1	TR	BGN	25015	5100	2601	T	51
MG.1.TR.BGN.25015.5100.2601.T....53.....51010	MG	1	TR	BGN	25015	5100	2601	T	53
MG.1.TR.BGN.25015.5100.2601.T....53.....LIVEDB	MG	1	TR	BGN	25015	5100	2601	T	53
MG.1.TR.BGN.25015.5100.2601.T....56.....51010	MG	1	TR	BGN	25015	5100	2601	T	56
MG.1.TR.BGN.25015.5100.2601.T....56.....LIVEDB	MG	1	TR	BGN	25015	5100	2601	T	56
MG.1.TR.BGN.25015.5100.2621.T....51.....51010	MG	1	TR	BGN	25015	5100	2621	T	51