Getting data in Qlikview

[#1]

Hello,

I have this kind of data in excel.

Sr.No.	PO.NO.	Date	Supplier	Site	Material	Qty.
1	ABC	01/12/2012	abc123	Nerul	A	11
					B	8
					C	6
					D	8
2	XYZ	40920	xyz123	Vashi	E	12
					F	16
					G	6
					H	6

When i take it in qlikview .I get

PO.NO.	Supplier	Site	Material	Qty.
ABC	abc123	Nerul	A	11
XYZ	xyz123	Vashi	E	12
			B	8
			C	6
			D	8
			F	16
			G	6
			H	6

Is there any way out to get proper data in qlikview without changing in excel.

Attaching the Files..

Thanks and Regards,

Priya

[jagan · Accepted solution]

Hi Priya,

Try this script

LOAD

    If(Len(Sr.No.) = 0, Peek('Sr.No.'), Sr.No.) AS Sr.No.,

    If(Len(PO.NO.) = 0, Peek('PO.NO.'), PO.NO.) AS PO.NO.,

    If(Len(Date) = 0, Date(Peek('Date')), Date) AS Date,

    If(Len(Supplier) = 0, Peek('Supplier'), Supplier) AS Supplier,

    If(Len(Site) = 0, Peek('Site'), Site) AS Site,

    If(Len(Material) = 0, Peek('Material'), Material) AS Material,

    Qty.;

LOAD Sr.No.,

     PO.NO.,

     Date,

     Supplier,

     Site,

     Material,

     Qty.     

FROM

Excel.xlsx

(ooxml, embedded labels, table is Sheet1);

Hope it helps you.

Regards,

Jagan.

[#3]

Hi,

In file wizard Click next and you get a option as "Enable transformation step" click that option and go to second tab "Fill", Select  Column 1 and click on fill button, next click on cell condition select option "is empty" and 'fill type" as above.

This will fill the above value to all the empty cell , report this step for all column.

your script should be same as below

FIll:

LOAD Sr.No.,

     PO.NO.,

     Date,

     Supplier,

     Site,

     Material,

     Qty.

FROM

Excel.xlsx

(ooxml, embedded labels, table is Sheet1, filters(

Replace(1, top, StrCnd(null)),

Replace(2, top, StrCnd(null)),

Replace(3, top, StrCnd(null)),

Replace(4, top, StrCnd(null)),

Replace(5, top, StrCnd(null))

));