topic Transforming a clunky txt report in QlikView

Transforming a clunky txt report

sibrulotte — Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: Transforming a clunky txt report

alex00321 — Tue, 24 Dec 2019 09:01:43 GMT

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!

Re: Transforming a clunky txt report

Brett_Bleess — Thu, 26 Dec 2019 14:09:32 GMT

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

Re: Transforming a clunky txt report

alex00321 — Mon, 30 Dec 2019 02:57:45 GMT

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!

Re: Transforming a clunky txt report

sibrulotte — Mon, 06 Jan 2020 19:28:02 GMT

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

Will let you know later.

Thx.

Re: Transforming a clunky txt report

Procopioo — Sun, 12 Jan 2020 10:26:22 GMT

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.