Repeating of values

[#1]

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

[#2]

Anyone please answer it

[Sokkorn]

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

[sadickbasha · Accepted solution]

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

[#5]

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