topic Re: Split single column hierarchy text data into 2 columns while load in QlikView

Split single column hierarchy text data into 2 columns while load

Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I need to load a single column text file having below data in it:

\\MAC\USERS\TEST1\CAT1
      HOME\GENERAL01
      HOME\GENERAL02
      HOME\GENERAL03
\\MAC\USERS\TEST2\CAT2
     HOME\ASHTH01
     HOME\ASHTH02
     HOME\ASHTH03

Need to split the above column into 2 columns in a separate resultant table.

Resultant table content should be

Col1             Col2
----------         ------------------
TEST1          HOME\GENERAL01
TEST1          HOME\GENERAL02
TEST1          HOME\GENERAL03

TEST2          HOME\ASHTH01
TEST2          HOME\ASHTH02
TEST2          HOME\ASHTH03

Thanks in advance,

Raj

Re: Split single column hierarchy text data into 2 columns while load

MarcoWedel — Wed, 09 Nov 2016 21:58:24 GMT

Hi,

maybe one solution could be:

SET Verbatim = 1;

table1:
LOAD If(LTrim(TextLine)=TextLine, SubField(TextLine,'\',5), Peek(Col1)) as Col1,
    If(LTrim(TextLine)<>TextLine, Trim(TextLine)) as Col2
Inline [
TextLine
"\\MAC\USERS\TEST1\CAT1"
"      HOME\GENERAL01"
"      HOME\GENERAL02"
"      HOME\GENERAL03"
"\\MAC\USERS\TEST2\CAT2"
"    HOME\ASHTH01"
"    HOME\ASHTH02"
"    HOME\ASHTH03"
];

Right Join LOAD Distinct Col2 Resident table1;

hope this helps

regards

Marco

Re: Split single column hierarchy text data into 2 columns while load

el_aprendiz111 — Wed, 09 Nov 2016 22:01:43 GMT

Hi,

Directory;
LOAD
'TEST1' AS COL1,
@1 AS COL2
FROM
[..\..\..\..\Desktop\web_Qlik\txt\CAT1.txt]
(txt, codepage is 1252, no labels, delimiter is '\t', msq);

LOAD
'TEST2' AS COL1,
@1 AS COL2
FROM
[..\..\..\..\Desktop\web_Qlik\txt\CAT2.txt]
(txt, codepage is 1252, no labels, delimiter is '\t', msq);

EXIT Script;

Re: Split single column hierarchy text data into 2 columns while load

Thu, 10 Nov 2016 03:26:25 GMT

Thanks Marco/fer.

marcowedel‌: Getting error saying field not found - <TextLine>

Re: Split single column hierarchy text data into 2 columns while load

Thu, 10 Nov 2016 03:26:53 GMT

Getting error saying field not found - <TextLine>

Re: Split single column hierarchy text data into 2 columns while load

MarcoWedel — Thu, 10 Nov 2016 22:16:25 GMT

didn't get any errors in my example.

Can you share your script to look for the reason of this behaviour?

Re: Split single column hierarchy text data into 2 columns while load

Anil_Babu_Samineni — Sat, 12 Nov 2016 13:54:11 GMT

Can you elaborate more. What are these

\\MAC\USERS\TEST1\CAT1