Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have a text file with blocks of data. If the first line is starting with a number and 8th value of the string has '#' then it is my header.
The records below that corresponds to that Value.These are '*' delimited. There can be any number of records until next header comes.
Can someone help create a for loop for this load. Attached text file.
Input Format:
Output to be Loaded in Qlik:
No need for a loop. You can do it like this:
Temp:
LOAD
If(IsNum(Left(@1, 1)) And Mid(@1, 8, 1) = '#', @1, Peek(Field1)) as Field1,
If(IsNum(Left(@1, 1)) And Mid(@1, 8, 1) = '#', 'X', @1) as Data
FROM
(txt, codepage is 1252, no labels);
Result:
LOAD Field1,
SubField(Data, '*', 1) as Field2,
SubField(Data, '*', 2) as Field3,
SubField(Data, '*', 3) as Field4,
SubField(Data, '*', 4) as Field5
Resident Temp
Where Data <> 'X';
DROP Table Temp;
No need for a loop. You can do it like this:
Temp:
LOAD
If(IsNum(Left(@1, 1)) And Mid(@1, 8, 1) = '#', @1, Peek(Field1)) as Field1,
If(IsNum(Left(@1, 1)) And Mid(@1, 8, 1) = '#', 'X', @1) as Data
FROM
(txt, codepage is 1252, no labels);
Result:
LOAD Field1,
SubField(Data, '*', 1) as Field2,
SubField(Data, '*', 2) as Field3,
SubField(Data, '*', 3) as Field4,
SubField(Data, '*', 4) as Field5
Resident Temp
Where Data <> 'X';
DROP Table Temp;