Hi all,
I have following data
| Voucher Type & No |
| Date |
| A/C No. : 10511 PETTY CASH -JEDDAH |
| JV | 91 | 01-Apr-11 |
| JV | 128 | 28-May-11 |
| CPV | 2 | 15-Aug-11 |
| CPV | 3 | 16-Aug-11 |
| CPV | 6 | 25-Aug-11 |
| CPV | 7 | 26-Aug-11 |
|
|
|
| Voucher Type & No |
| Date |
| A/C No. : 10512 PETTY CASH - OPERATIONS DEPT. |
| JV | 409 | 17-Sep-11 |
I need in this format
| Account Name | Account No | Voucher Type | Voucher No | Date |
| PETTY CASH -JEDDAH | 10511 | JV | 91 | 01-Apr-11 |
| PETTY CASH -JEDDAH | 10511 | JV | 128 | 28-May-11 |
| PETTY CASH -JEDDAH | 10511 | CPV | 2 | 15-Aug-11 |
| PETTY CASH -JEDDAH | 10511 | CPV | 3 | 16-Aug-11 |
| PETTY CASH -JEDDAH | 10511 | CPV | 6 | 25-Aug-11 |
| PETTY CASH -JEDDAH | 10511 | CPV | 7 | 26-Aug-11 |
| PETTY CASH - OPERATIONS DEPT. | 10512 | JV | 409 | 17-Sep-11 |
I also want to omit blank rows having no voucher.
I wrote the below script but cannot populate the Account name and account no fields and cannot omit blank rows
LOAD if((left([Voucher Type & No],6))='A/C No',RIGHT(LEFT([Voucher Type & No],16),5),) as [Account No],
if((left([Voucher Type & No],6))='A/C No',RIGHT([Voucher Type & No],LEN([Voucher Type & No])-18),) as [Account Name],
if((left([Voucher Type & No],6))<>'A/C No',[Voucher Type & No],)) as [Voucher Type],
F2 as [Voucher No],
Date,
FROM
(ooxml, embedded labels, header is 2 lines, table is Sheet4);
