topic Re: Repeating of values in QlikView

Repeating of values

Mon, 11 Nov 2013 06:15:39 GMT

Hi all,

I am new in Qlikview. I have multiple excel file to load and all excel files are same as like below:

Country	S.NO.	Name
America	1	John
	2	Emmy
	3	Michael
Canada	1	Sara
	2	Emma
	3	Marry
	4	Jack
	5	Rose

and i want that after loading all the files i want this transformation of data in qlikview which is below:

Country	S.NO.	Name
America	1	John
America	2	Emmy
America	3	Michael
Canada	1	Sara
Canada	2	Emma
Canada	3	Marry
Canada	4	Jack
Canada	5	Rose

Kindly Help me out as soon as possible that who can i transform data as above while i perform loading.

Thanks in advance

Re: Repeating of values

Mon, 11 Nov 2013 06:27:33 GMT

Anyone please answer it

Re: Repeating of values

Sokkorn — Mon, 11 Nov 2013 06:29:02 GMT

Hi Syed,

Using Peek() function should do this job. Here is an example:

[Data]:

LOAD * Inline [

Country, SO, Name

America, 1, John

, 2, Emmy

, 3, Michael

Canada, 1, Sara

,2, Emma

,3, Marry

,4, Jack

,5, Rose];

[Data2]:

LOAD

Country AS Country1,

If(Len(Country)=0,Peek(Country2),Country) AS [Country2],

SO AS SO1,

Name AS Name1

Resident [Data];

Regards,

Sokkorn

Re: Repeating of values

sadickbasha — Mon, 11 Nov 2013 06:39:27 GMT

Yes, as sokkom mentioned the Peek() will resolve. here is the simplified code

LOAD
if(len(Country)=0,Peek(Country),Country) as Country,
S.NO.,

Name;

LOAD Country,

S.NO.,

Name
FROM

C:\Users\260658\Desktop\test2.xlsx

(ooxml, embedded labels, table is Sheet9);

Re: Repeating of values

Mon, 11 Nov 2013 06:53:31 GMT

Thanks So much guys it works.. I am so kind of u..