Qlik Community

Ask a Question

Connectivity & Data Prep

Discussion board where members can learn more about Qlik Sense Data Connectivity.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
tfrazerheartlan
Contributor III
Contributor III

ETL Text File

I have a text file with the below data format. I'm trying to find the best way to extract the Number/Name/Amount that I have highlighted below. Data for each company is between the hyphens.

Has anyone had any success on extracting from a file similar to this data format?

Qlik_Text_ETL.png

6 Replies
tush
Creator II
Creator II

Hi,

     can you please provide sample text file

 

Thanks

Tushar

tfrazerheartlan
Contributor III
Contributor III
Author

For some reason .txt is not a valid file type to attach here?

tfrazerheartlan
Contributor III
Contributor III
Author

Qlik_Text_ETL_Error.png

tfrazerheartlan
Contributor III
Contributor III
Author

Attaching zip file

tfrazerheartlan
Contributor III
Contributor III
Author

Was just curious if you ever had a chance to check out that attached file?

marcus_sommer
MVP & Luminary
MVP & Luminary

I think it looked more difficult as it is. Important ist just to find regularities within the records. If the data-structure is always the same and each entry has the same number of records you could just use recno() to identify/filter your needed records. If they are different in some way you need to search within the content of a record.

In your case I think I would load the file with fix length (is an option within the file-wizard) and apply a wildmatch as filter. The second step could be a cleaning of the multiple spaces with a mapping, the third picked the appropriate values with a subfield, fourth collect the various values with an interrecord-function like previous within a single record and the fifth step removed the invalid records with a flag field which is created/maintained through the load-chain.

load * where FLAG = true();
load *, previous(F2) as F3;
load F, subfield(F, ' ', NUMBER) as F1, subfield(F, ' ', NUMBER) as F2;
load mapsubstring('map', F) as F;
load @1:n as F from Source where wildmatch(@1:n, '*DATE Opened*', '*TOTAL:*');

A quite similar example which used such mapping on spaces could you find here:

Issue-with-loading-special-character

- Marcus