Split single column hierarchy text data into 2 columns while load

[#1]

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

[MarcoWedel]

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

[el_aprendiz111]

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;

[#4]

Thanks Marco/fer.

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

[#5]

Getting error saying field not found - <TextLine>

[MarcoWedel]

didn't get any errors in my example.

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

[Anil_Babu_Samineni]

Can you elaborate more.  What are these

\\MAC\USERS\TEST1\CAT1