Generate Missing Data - A Challenger!

Wed, 17 Jul 2013 14:49:20 GMT

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

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

topic Generate Missing Data - A Challenger! in QlikView