topic Re: How convert text file into tabular format in Qlik sense in App Development

How convert text file into tabular format in Qlik sense

TauseefKhan — Tue, 15 Mar 2022 11:16:55 GMT

I have data in text fiel in below format:

----------NEW TRANSACTON-----------
TRANSACTION DATE : 10:02 AM, 05 Dec 2021
CLIENT ID : Z0119
CLIENT NAME : ABCZ SALE
AMOUNT : 180,650.00
TRANSACTION REF. : 021

I need data in below format:

Re: How convert text file into tabular format in Qlik sense

NitinK7 — Tue, 15 Mar 2022 11:55:17 GMT

try below script

but make sure you have add only delimiter at one time , in your data TRANSACTION DATE : 10:02 AM, 05 Dec 2021

you use same delimiter 2 time. try like TRANSACTION DATE : 10.02 AM, 05 Dec 2021 then below script will work

ABC:

LOAD [TRANSACTION DATE],
[CLIENT ID],
[CLIENT NAME],
AMOUNT,
[TRANSACTION REF.]
FROM
[lib://desk/ss.txt]
(txt, codepage is 28591, embedded labels, delimiter is ':', msq, filters(Transpose()));

Re: How convert text file into tabular format in Qlik sense

MarcoWedel — Tue, 15 Mar 2022 18:06:55 GMT

Assuming there are multiple transactions included in one file, one solution might be:

using this test file:

tabTransactFile: LOAD [@1:n] as Line, RangeSum(Peek(TransactID),-([@1:n]='----------NEW TRANSACTON-----------')) as TransactID FROM [lib://DataFiles/Transactions.txt] (fix, codepage is 1252); tabTransactTemp: Generic Load TransactID, SubField(Line,':',1), Mid(Line,Index(Line,':')+1) Resident tabTransactFile Where Line<>'----------NEW TRANSACTON-----------'; Drop Table tabTransactFile; tabTransact: Load '' as TempField AutoGenerate 0; FOR i = NoOfTables()-1 to 0 STEP -1 LET vTable=TableName($(i)); IF WildMatch('$(vTable)', 'tabTransactTemp.*') THEN JOIN (tabTransact) LOAD * RESIDENT [$(vTable)]; DROP TABLE [$(vTable)]; ENDIF NEXT i Drop Field TempField;

hope this helps

Marco

Re: How convert text file into tabular format in Qlik sense

TauseefKhan — Wed, 16 Mar 2022 04:54:17 GMT

When I use Mid() function or this Expression: Mid(Line,Index(Line,':')+1) showing An error occurs: the session has timed out due to inactivity.

Re: How convert text file into tabular format in Qlik sense

MarcoWedel — Wed, 16 Mar 2022 20:55:16 GMT

I don't think this error message is related to the Mid() function.

Did you try reopening your session and repeating the test?