Reading data from .txt

[Anonymous]

I have a flat file with rows representing tables.

The 1st table where the 1st two characters are 00 is the invoice and supplier details.

If the 1st two characters are between 1 and 50 then it is the second table with the allocations and amounts.

So the invoice can be used to pay up to 50 times, different amounts and different allocations.

The challenge im facing is mapping the invoice information to relevant information in table two.

In the attached .txt there are two invoices. Under each invoice is the allocations information.

I also attached the qvw. Please assist

[woshua5550]

can you provide your expected output ?

[Anonymous]

Below is my expected output, but instead it adds up the Amount for allocation 1 for both invoices and make it an amount for 1 invoice. same goes with allocation 2,3,4, and 5.

Record_Number	Supplier_Name	Invoice_Number	Location_Allocation	Project_Allocation	Allocation_Number	Amount
						65 388.24
00	GAO PTY LTD	INV1526	1238	8962	01	16 356.50
00	GAO PTY LTD	INV1526	5214	8523	02	1 966.50
00	GAO PTY LTD	INV1526	7852	8230	03	2 126.33
00	GAO PTY LTD	INV1526	4789	0987	04	1 200.00
00	GAO PTY LTD	INV1526	4836	7892	05	5.83
00	Yak PTY LTD	INV2266	1244	7762	01	20 056.30
00	Yak PTY LTD	INV2266	5214	8523	02	8 644.50
00	Yak PTY LTD	INV2266	7852	8230	03	5 623.45
00	Yak PTY LTD	INV2266	4789	0987	04	8 952.00
00	Yak PTY LTD	INV2266	4836	7892	05	456.83

[woshua5550 · Accepted solution]

hope this fulfill your reqirement

LOAD RowNo() as ID,
  [@1:2] as Record_Number,
    [@3:8] as Document_Type,
    [@9:15] as Invoice_Number, 
    [@17:24] as Document_Date,
    [@27:37] as Supplier_Name,
    [@48:65] as Supplier_Address,
    [@69:76] as City
FROM
[Dummy_data.txt]
(fix, codepage is 1252)
Where [@1:2]=00 ;

Left Join

LOAD Ceil(RowNo()/5) as ID,
  [@1:2] as Allocation_Number,
    [@9:16] as Amount,
    [@23:26] as  Location_Allocation,
    [@33:37] as Project_Allocation
FROM
[Dummy_data.txt]
(fix, codepage is 1252)
Where [@1:2]<>00;

DROP Field ID;

check my test file attached

[Anonymous]

Thanks a lot Dave, It works very well

[Anonymous]

Hi Dave, what if we have different number of records below 00 row?

Like for invoice1 we have 01 and 02 only, For invoice 2 we have 01,02,03, and for invoice 3 we have 01 only?

What can we replace the 5 with in the below function?

Ceil(RowNo()/5

Thanks

[woshua5550]

Oh,that would be quite different, check this

[Anonymous]

Thanks a lot Dave

[Anonymous]

Hi Dave,

I have the same challenge. I implemented your solution but it only works when I read data from one file.

I have about 800 txt files and when I replace the file name with * in the script it doesn’t give the correct results.

Please assist

[woshua5550]

Just get rid of "Right Join" ,Try this

Dummy_data:
LOAD RowNo()-[@1:2] as ID1,
  [@1:2] as Allocation_Number,
     [@9:16] as Amount,
     [@23:26] as  Location_Allocation,
     [@33:37] as Project_Allocation
FROM
[*.txt]
(fix, codepage is 1252)
Where [@1:2]<>00;

Left Join

LOAD ID1,Count(0) as ID2, RowNo() as ID
Resident Dummy_data
Group by ID1;

Dummy_data2:
LOAD RowNo() as ID,
  [@1:2] as Record_Number,
     [@3:8] as Document_Type,
     [@9:15] as Invoice_Number, 
     [@17:24] as Document_Date,
     [@27:37] as Supplier_Name,
     [@48:65] as Supplier_Address,
     [@69:76] as City
FROM
[*.txt]
(fix, codepage is 1252)
Where [@1:2]=00 ;

DROP Fields ID1,ID2;