Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator III
Creator III

Transforming a clunky txt report

Hi, 

I have a txt file with super bad ass 1995 style report headers by product group that I want to transform as a colum in my load.

 

Report name

Product: 1010

Account    Ammount    Date

123                      50$          2019-12-29

Product: 1020

Account    Ammount    Date

123                      50$          2019-12-29

 

And so forth.

How do I get my product loaded as a column?

5 Replies
Highlighted
Creator II
Creator II

Hi, I think the point here would be firstly transform your data into structure way so Qlik can consume. Try to use Excel to open the txt file and do the formatting. Thanks!

Highlighted
Digital Support
Digital Support

You can use the transform wizard in the Script Editor to help you get rid of the the rows in the report you do not want, it may take some trial and error to get it right, but that is likely the best way to go at that part of things.  I am including a design blog link below for the cross-table piece, which I think you will also need.

https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/FileWizard.htm

https://community.qlik.com/t5/Qlik-Design-Blog/The-Crosstable-Load/ba-p/1468083

Hopefully this gets you closer to what you need.

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
Highlighted
Creator II
Creator II

If your data is always in that format, try my below scripts:

tblTest:
LOAD @1 as content
FROM
[..\Test.txt]
(txt, codepage is 28591, no labels, delimiter is '\t', msq);

tempHeader:
LOAD trim(SubField(content,':',2)) as Product,RowNo() as key
Resident tblTest where wildmatch(content,'Prod*')=1;

tempContent:
LOAD content as newContent,RowNo() as key1
Resident tblTest where wildmatch(content,'Prod*')=0 and WildMatch(content,'Acc*')=0;

NewContent:
LOAD trim(SubField(newContent,' ',1)) as Account,
Trim(SubField(newContent,' ',-1)) as Date,
Trim(SubField(trim(SubField(newContent,'$',1)),' ',-1))&'$' as Amount,
key1 as key
Resident tempContent;

Left Join(tempHeader)
LOAD Product Resident tempHeader;

DROP Table tempContent;
DROP Table tempHeader;
DROP Table tblTest;

 

Thanks!

 

 

Highlighted
Creator III
Creator III

haven't tried it, but that sounds like a nice solution!

Will let you know later.

Thx.

Highlighted
Contributor
Contributor

In the event that GeekSeller asks you for a file from Amazon or from another system tellsubway, some systems can export TXT files that GeekSeller is unfortunately unable to import without adjustment.