topic Previous value in script load Excel file in QlikView

Previous value in script load Excel file

Wed, 28 Sep 2016 11:33:39 GMT

Hi all,

I have scenario in which i have to load numbers of phones in script. But format of Excel File is complicated:

Number	Traffic	Limit
9308140233	324	200
	11

		100
	76
9308140555	11	300
	22	300
	44	100
9308140777	32	100
	23	0

So I need in load script fulfill the 'Number' field using Previous function. The first value of phone number need to be inserted in below empty spaces , untill new number.

I want this result:

Number	Traffic	Limit
9308140233	324	200
9308140233	11
9308140233
9308140233		100
9308140233	76
9308140555	11	300
9308140555	22	300
9308140555	44	100
9308140777	32	100
9308140777	23	0

Thank you

Re: Previous value in script load Excel file

Wed, 28 Sep 2016 11:34:55 GMT

so I expect this result:

Number	Traffic	Limit
9308140233	324	200
9308140233	11
9308140233
9308140233		100
9308140233	76
9308140555	11	300
9308140555	22	300
9308140555	44	100
9308140777	32	100
9308140777	23	0

Re: Previous value in script load Excel file

muniyandi — Thu, 24 Nov 2016 23:23:06 GMT

Hi Try this code

Temp:

LOAD

Number,

Traffic,

Limit

FROM [lib://Muni/half result.xlsx]

(ooxml, embedded labels, table is Sheet3);

NoConcatenate

NewPeek:

Load RecNo() as No,Number,if(IsNull(Number),Peek(Number1,-1),Number) as Number1,Traffic,

Limit

Resident Temp;

Drop Table Temp;

Re: Previous value in script load Excel file

tamilarasu — Fri, 25 Nov 2016 02:32:45 GMT

Hello Alexander,

Just play with the Enable Transformation step in the File wizard, you will get the required output.

Data:
LOAD RecNo() as RowNo,
    Number,
    Traffic,
    Limit
FROM
Book11.xlsx
(ooxml, embedded labels, table is Sheet1, filters(
Replace(1, top, StrCnd(null))
));

Re: Previous value in script load Excel file

Fri, 25 Nov 2016 08:59:00 GMT

It help ! Mucho gracias !

Re: Previous value in script load Excel file

Fri, 25 Nov 2016 09:00:30 GMT

It ' s not working .

I get this :

Number1	Traffic	Limit
9308140233	324	200
9308140233	11

		100
	76
9308140555	11	300
	22	300
	44	100
9308140777	32	100
	23	0