Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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);
Anyone please answer it
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
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);
Thanks So much guys it works.. I am so kind of u..