topic Peek function in txt files in QlikView

Peek function in txt files

BSCIknowlec — Wed, 31 Mar 2021 14:22:15 GMT

hi all,

i have a txt file that im reading in, im doing a manually mid(raw data) to separate my columns. its simple in that most of the data i need are all in the same line, so i just need to parse out the column number.

however i do have an issue, i have an order# that is in the heading that needs to be assigned to all relevant lines that im pulling in until a new heading comes up with a new order number etc..

i assume the easiest way to do this would be to do a peek function to create a separate table just for my order #s , get the min and max and join it to my main details table. put a where clause in with the min and max of the lines?

the only thing in common with the orders is that it will always start on col 16 and be 9 in length. with it starting on col 1 as "Order Number: " below is a screenshot of the txt file. so from the screenshot, order number 1000069385 could be attached to each line that i bring in. then there will be a new order number: 100069387 and then that number will be attached to the next lines im bringing in etc

would anyone know a good way to capture all my order #s in the txt file and then put it against all the lines of data until there is a new order number?

thanks

Re: Peek function in txt files

rwunderlich — Wed, 31 Mar 2021 16:24:17 GMT

In the Load you can do something like:

if (left(raw, 14) = 'Order Number:'
,TextBetween(raw, ': ', ' ') // New order number
,Peek('OrderNumber') // Use current ordernumber
) as OrderNumber

This sample does something similar.

Qlikview Cookbook: Load Input File Containing Multiple Record Types https://qlikviewcookbook.com/recipes/download-info/load-input-file-containing-multiple-record-types/

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

Re: Peek function in txt files

BSCIknowlec — Wed, 31 Mar 2021 16:52:26 GMT

Hi @rwunderlich , thanks for that. Ive actually made it easier for me to play around with by using the wizard file and parsing out the columns. so my code looks like this in my script:

Details:
LOAD [@1:7] as Account,
[@12:32] as [Ac Desc],
num([@40:55]) as [Doc Number],
[@70:94] as [Vendor],
[@143:173] as [Desc Text],
[@212:222] as [PO],
[@34:36] as [Type],
[@61:68] as Month,
num([@97:113]) as [Value],
[@98:113] /.8558 as Dollars
FROM
txtfile
(fix, codepage is 1252, no labels)
where num([@1:7]);

Orders:
load
RowNo() as RawRowOrderNo,
If(RowNo() = 1, 0, Peek(RawRowOrderNo)) as prev, [@16:26] as Orders
FROM
txtfile
(fix, codepage is 1252, no labels)
where num([@16:26]) > 100000; "

so my details table has all the correct data in it and my orders table has all the correct order numbers in it now as well. so now i just need to connect the correct order number to each line of data

ive been playing around with the peek function with little success so far today

would your suggested method still work or is there another way??

thanks again

Re: Peek function in txt files

rwunderlich — Wed, 31 Mar 2021 18:02:31 GMT

I'm not following exactly what you are trying to do with RowNo(). I think you are trying to determine the range of lines associated with the order, but that is the hard way to go. Here's what I suggest you change. Remove the filtering from the main load. Propagate the order number through the rows. Then filter to only valid account rows using preceding load.

Details:
LOAD
*,
Where IsNum(Account)
;
LOAD
if (left[@1:n], 14) = 'Order Number:', [@16:26], Peek('OrderNumber')) as OrderNumber,
[@1:7] as Account,
[@12:32] as [Ac Desc],
num([@40:55]) as [Doc Number],
[@70:94] as [Vendor],
[@143:173] as [Desc Text],
[@212:222] as [PO],
[@34:36] as [Type],
[@61:68] as Month,
num([@97:113]) as [Value],
[@98:113] /.8558 as Dollars
FROM
txtfile
(fix, codepage is 1252, no labels)
;

-Rob

Re: Peek function in txt files

BSCIknowlec — Wed, 31 Mar 2021 18:34:04 GMT

hi Rob,

yes that was what i was trying to do, i tried your method but its throwing me up synthetic tables and not pulling in the correct data(obviously something im doing wrong). i have attached the txt file as a excel file to help better understand the sample data.

i would be very grateful if possibly you could you take a look at it and see where im going wrong? apologies im a bit of a newbie with editing txt files in qlik!

end result is for my data to look like this(but with a order field with the correct order #)

my code after updating with your edits:

Details:
Load *
//LOAD *,
FROM
TXT File
(fix, codepage is 1252, no labels)
where num([@1:7]);

LOAD
if( left([@1:n],14) = 'Order Number:', [@16:26], Peek('OrderNumber')) as OrderNumber,
[@1:7] as Account,
[@12:32] as [Ac Desc],
num([@40:55]) as [Doc Number],
[@70:94] as [Vendor],
[@143:173] as [Desc Text],
[@212:222] as [PO],
[@34:36] as [Type],
[@61:68] as Month,
num([@97:113]) as [Value],
[@98:113] /.8558 as Dollars
FROM
TXT File
(fix, codepage is 1252, no labels)
where num([@16:26]) > 100000;"

Re: Peek function in txt files

rwunderlich — Wed, 31 Mar 2021 20:02:34 GMT

Look at my example again. The top LOAD statement should not have the

FROM
TXT File
(fix, codepage is 1252, no labels)

It is a preceding load, a continuation of the one below.

-Rob

Re: Peek function in txt files

BSCIknowlec — Wed, 31 Mar 2021 20:26:08 GMT

apologies rob you are correct. once i dropped that it pulled in my 97 rows that i need. however im still getting null values for the order number section?

would you have any idea as to why?

thanks for troubleshooting this for me so far its really helpful

Re: Peek function in txt files

rwunderlich — Wed, 31 Mar 2021 21:58:01 GMT

My bad. Peek() won't work correctly in a preceding load. You will have to filter with an Inner Join after loading all rows. This works for me:

Details:
LOAD
if( [@1:14] = 'Order Number:', [@16:26], Peek('OrderNumber')) as OrderNumber,
[@1:7] as Account,
[@12:32] as [Ac Desc],
num([@40:55]) as [Doc Number],
[@70:94] as [Vendor],
[@143:173] as [Desc Text],
[@212:222] as [PO],
[@34:36] as [Type],
[@61:68] as Month,
num([@97:113]) as [Value],
[@98:113] /.8558 as Dollars
FROM [TXT DATA.txt]
(fix, codepage is 1252, no labels)
;
Inner Join (Details)
Load *
Resident Details
where IsNum(Account)
;

-Rob