Qlik Community

Ask a Question

Qlik Sense Advanced Authoring

Discussion board where members can learn more about Qlik Sense Advanced Authoring.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor
Contributor

Load data from Log file

Hello,

We have an external sales system that cannot be connect to Qlik. However, they can provide a log file for each order placed in the system. This log file can be opened via csv or txt but is one long string.

If I am going to use this file to help understand orders from this external system, I need a way to break this file into columns and rows. I am looking for ideas on how I would do that.

I am looking to only pull some information out of this file, not all the fields are valuable. For instance, this order has 35 items and each has a product_id. I would want to pull the text "product_id" and turn that into a column and in the rows have each of the product_id numbers. Same idea for product_name, product_upc, and other fields that would be meaningful. 

I've tried the subfield expression but have only been able to pull the first instance of product_id, not all of them in the string.

If anyone could help me with any ideas on breaking out the product_id field and data I can take it from there. I've attached a sample log file, if that helps.

Thank you for your help!

Labels (3)
2 Replies
Luminary
Luminary

Hi Paul,

There is an easy way to do this but this requires a bit work on your side. I have done the same thing to analyse logs from our VPN. As you said the log is one single long string which needs to be broken down to several new fields which will have business meaning to you. To do so you can use the function SubField (“LogField” , ‘Seperator’, Position). The first parameter is the field you want to extract your subfield from. Second parameter is the separator. It can be string composed of one or several characters or even longer if needed. The last parameter is the position. 1 will extract everything before the first seperator. 2 between the 1st seperator and the second (or the end of the string) and so on. You will use several subfield functions  to achieve your goal. You also also use the function purgechar to get rid of unwanted characters in your string. Replace might also be useful.

Contributor
Contributor

Henri,

Thank you for the quick response. I'll keep working on the subfield function to see if I can get it to work for all the data in the file. I was working with that function yesterday and I could get it to pull the first instance of the field but not each of them (There should be around 34 unique product id's in the file but I could only pull the first one with what I was doing).

I'll update you if I was able to get it figured out.

Thanks!