Transforming a clunky txt report

[sibrulotte]

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?

[alex00321]

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!

[Brett_Bleess]

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

[alex00321]

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!

[sibrulotte]

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

Will let you know later.

Thx.

[Procopioo]

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.