Loading data from multiple lines

[taylormjd]

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?

[Anonymous]

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

[taylormjd]

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.

[Anonymous]

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

[prabhu0505]

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

[taylormjd]

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).

[taylormjd · Accepted solution]

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:

1. 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.
2. 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.
3. 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]