Load Text File with Complex Structure

[STMW]

Hi Everyone,

I am new to Qlik Sense and i want to load a text file with a specific format. For instance, in the text file i have :

Incident #INCS56
Status:
Closed Skipped
Substate:
Permanently Resolved
Priority:
4
Created:
19 May 22
Last Updated:
31 May 22

And i wish to load in qlik in a table with columns (Incident, Status, Substrate, Priority, Created, Last Updated)

Their respective values are :   #INCS56, Closed Skipped, Permanently Resolved, 4, 19 May 22, 31 May 22.

[Andrei_Cusnir]

Hello,

I believe that your exact use case scenario is not possible to achieve only with Qlik Sense. As you can see, Qlik Sense has specific file formats that it can parse and also playing around with the configuration, it seems that it is impossible to read the structure of your data properly.

It looks that the easiest way to achieve your outcome is:

1. Create a small Python script that will parse the data and store it in arrays in memory
2. Use Pandas to save the parsed data as CSV or EXCEL file
3. Import CSV/EXCEL file in Qlik Sense

It shouldn't be hard to build the script in Python, so I believe that it is the easiest solution for your specific (complex) use case scenario. 

I hope that this information was helpful. In case I have misunderstood the use case scenario, please elaborate in details by providing additional information. However, if it has helped you resolve the issue, addressed your concerns or at least pointed you in the right direction, please mark it as Accepted Solution to give further visibility to other community members. 
 

[marcus_sommer]

I think there are various approaches possible but it will depend on your real data which one is more suitable respectively requires which efforts. If all data-sets have always 11 rows you could use something like this:

mod(rowno(), 11) as RowNoMod

to identify the content of each record. The RowNoMod information could be then used as record-index within a peek() to grab the values of the previous records. The 11th record would then the final record and all others could be deleted - afterwards.

If there not always 11 records - even after some additionally cleaning/preparing - the mod-identifying will fail. In such a case you could create a data-set flag, for example with something like:

t1: load *, if(left(Field, 8 ) = 'Incident', rangesum(peek('Flag'), 1), peek('Flag')) as Flag, rowno() as RowNo
from X;

Afterwards you could aggregate your data based on this flag-field and using some strong-functions, for example subfield() to extract the values again, maybe with something like this:

t2: load Flag, concat(Field, '|', RowNo) as Field resident t1 group by Flag;

t3: load *, subfield(Field, '|', 1) as Incident, subfield(Field, '|', 3) as Status, ... resident t2;

You may need here and there some adjustments but the general logic will work. The easiest way would be you copy two or three of the data-sets into a separate txt-file and then playing with a new application and working from step to step to load and transform the data.

- Marcus

[MarcoWedel]

Is there only one record/incident per file?

Please post a larger sample.

[STMW]

Thanks for your reply @Andrei_Cusnir but i am only allowed to use qlik sense.

[STMW]

Hi @marcus_sommer ,

I don't understand the <<Flag>> and <<Field>> term you use in t1: load *, if(left(Field,= 'Incident', rangesum(peek('Flag'), 1), peek('Flag'))

[marcus_sommer]

It are just field names.

Field is the field which you are loading from the txt-file. If there are no labels included it would probably recognised with @1 and in the final meaning like:

t: load @1 as Field from X;

Just use the wizard for this step and then adjusting it like you want.

Flag is the name of a new created field to flag a complete data-set to a single number.

- Marcus

[STMW]

I have these 3 sample files and i wish to extract their information in a table(Incident, Status, Substrate, Priority, Created, Last Updated)

[marcus_sommer · Accepted solution]

If there is really a single data-set within a single file the data could be simply joined with appropriate where-clauses on the record, like (the inline-statement is just for demonstration purpose):

t: load *, recno() as RecNo inline [
F
Incident #INC2524841
Status:
Closed Skipped
Substate:
Permanently Resolved
Priority:
4
Created:
19 May 22
Last Updated:
31 May 22
];

t2: load F as Incident resident t where RecNo = 1;
join (t2) load F as Status resident t where RecNo = 3;
join (t2) load F as SubStatus resident t where RecNo = 5;
....

This might be done within a filelist-loop whereby the final tables needs to stored and dropped in each iteration and afterwards they could be loaded completely again.

- Marcus

[STMW]

Thanks @marcus_sommer  it worked.