topic Re: Getting data in Qlikview in QlikView

Getting data in Qlikview

Fri, 26 Apr 2013 12:08:26 GMT

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

Re: Getting data in Qlikview

jagan — Fri, 26 Apr 2013 12:33:34 GMT

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.

Re: Getting data in Qlikview

Fri, 26 Apr 2013 13:01:54 GMT

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

));