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

[HWallays]

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 

[jyothish8807]

Hi Hwallays,

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

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

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.

[marcus_sommer]

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

...

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

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

...

- Marcus

[HWallays]

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