topic Re: Loading data from multiple lines in QlikView

Loading data from multiple lines

taylormjd — Thu, 29 Oct 2015 18:30:54 GMT

I have a report saved to a text file that I'm loading into Qlikview. I'm using a WHERE clause to exclude the non-detail records (page headers, detail headers, totals, etc.) from the report. But there are 2 fields (Company Code and Statutory Code) that are embedded in the page headers that I need to include with the detail fields that I'm loading.

For simplicity sake, let's assume the report looks like the following:

Page 1 Report Header

Company Code: ABC

Statutory Code: 123

Policy Number Account Number Amount

XYZ-123 111111 10.00

XYZ-999 222222 100.00

**TOTAL* 110.00

Page 2 Report Header

Company Code: ZZZ

Statutory Code: 456

Policy Number Account Number Amount

CCC-111 333333 20.00

KKK-888 444444 200.00

**TOTAL* 220.00

I need for my loaded data table to look like this (red font used to emphasis where the data comes from on the report):

Company Code	Statutory Code	Policy Number	Account Number	Amount
ABC	123	XYZ-123	111111	10.00
ABC	123	XYZ-999	222222	100.00
ZZZ	456	CCC-111	333333	20.00
ZZZ	456	KKK-888	444444	200.00

Is there a way to accomplish this goal?

Re: Loading data from multiple lines

Anonymous — Thu, 29 Oct 2015 18:47:22 GMT

Rather than looking for color code you can add an additional like to your.script to identify the data coming from which.sheet (as mentioned below)

'Sheet1' as sheet_info

Re: Loading data from multiple lines

taylormjd — Thu, 29 Oct 2015 19:23:40 GMT

I'm sorry. I wasn't clear with my color code comment.

I'm not wanting to actually include that in the data table. I was just doing it to point out the fields that I was needing to pull from someplace other than the detail line.

Re: Loading data from multiple lines

Anonymous — Thu, 29 Oct 2015 19:33:23 GMT

Then simply delete those fields while loading the data in qlikview. Also, share the sample data to guide you in better note, if this doesn't solve your concern

Re: Loading data from multiple lines

prabhu0505 — Thu, 29 Oct 2015 19:43:34 GMT

first 1 // need only one row

LOAD

@2 // only column B

FROM test.xls (biff, header is 4 lines, no labels, table is Sheet1$) // goes to line 5

how to read a particular cell from an excel sheet using Qlikview

Re: Loading data from multiple lines

taylormjd — Thu, 29 Oct 2015 19:51:33 GMT

I apologize that I seem unable to make myself clear in my question.

I've attached my actual report extract text file.

For my end result, I want my data table to include all the fields in the detail lines (Policy Number, Account Number, Account Description, Item ID, Pr Cd, Plan Code, Trx Eff Date, Tran Code, Memo Code, Sorc Code, Debits, Credits), as well as 2 fields fromm the page header (Company Code that's in position 33-35 from the row that has CYCLE DATE in position 7-16 and the Statutory Code that's in position 19-21 from the row that has STATUTORY CODE in position 4-17).

Re: Loading data from multiple lines

taylormjd — Fri, 30 Oct 2015 20:19:42 GMT

I have resolved my issue by utilizing the Peek command.

I removed the WHERE clause from my LOAD statement that prevented anything other than detail records in my report from loading. Then as each record was loaded, I checked specific positions to identify where my Page Header information was found in my report (i.e. records with CYCLE DATE in position 4:17 contain my COMPANY field in position 32:33 and records with STATUTORY CODE in position 4:17 contain my STATCODE field in position 19:21).

I added 3 new lines to my load script:

As I read a CYCLE DATE record, I loaded the desired positions into my COMPANY field, then used the Peek command to copy the COMPANY into all other records until I read the next CYCLE DATE record.
And as I read a STATUTORY CODE report, I loaded the desired positions into my STATCODE fields, then used the Peek command to copy the STATCODE into all other records until I read the next STATUTORY code record.
I loaded another fields (RECTYPE) to identify the detail records from the "other" (Page Header and total) records, so that I could excude the "other" records from my reports, since I couldn't exclude them from the load.

This is my LOAD command...

LOAD

If(Text(@4:17)='CYCLE DATE',Text(@32:34),Peek(COMPANY)) as [COMPANY],
If(Text(@4:17)='STATUTORY CODE',Text(@19:21),Peek(STATCODE)) as [STATCODE],
If(Text(@78:78)='/','Detail','Other') as [RECTYPE],
Text(@1:16) as [POLICY NUMBER],
     Text(@17:26) as [ACCOUNT NUMBER],
     Text(@29:59) as [ACCOUNT DESCRIPTION],
     Text(@60:65) as [ITEM ID],
     Text(@66:68) as [PR CD],
     Text(@69:75) as [PLAN CODE],
     @76:86 as [TRX EFF DATE],
     Text(@87:91) as [TRAN CODE],
     Text(@92:96) as [MEMO CODE],
     Text(@97:100) as [SORC CODE],
     @101:115 as [DEBITS],
     @116:n as [CREDITS]