Straight Table - Merging multiple rows into one row in multiple columns - Not Working

[Lavanya]

Hi All,

I am very new to Qlik View. I have below requirement with details shown.

Name        ID        Email                                CountofSubject         Location         Subject        SubjectStatus

Xavier       123      xavier@xyz.com            1                                     Germany         German          Completed
Xavier       123      xavier@xyz.com            1                                     France              French            Completed
Monica     675      monica@xyz.com        1                                      Spain                Spanish          In Progress
Monica     675      monica@xyz.com        1                                      England           English           Completed
Ana            456      ana@xyz.com                1                                      Germany         German          In Progress

I want to merge the rows like below.

Name     ID       Email       CountofSubject     Location1    Subject1    SubjectStatus1     Location2   Subject2   SubjectStatus2

Xavier     123      xavier@xyz.com         2               Germany      German       Completed           France           French       Completed
Monica   675      monica@xyz.com      2               Spain             Spanish       In Progress           England        English      Completed
Ana         456      ana@xyz.com              1                Germany      German       In Progress

Please find the attached application Chart_Req.qvw also image for data

Help is always appreciate. Thanks in advance!

[Vegar]

Try this script:

Data:
LOAD Name,ID,Email,	
SUM(CountofSubject) as CountofSubject,	
FirstSortedValue(Location, Row) as [Country 1],
FirstSortedValue(Subject, Row) as [Subject 1],
FirstSortedValue(SubjectStatus, Row) as [SubjectStatus 1],
FirstSortedValue(Location, Row,2) as [Country 2],
FirstSortedValue(Subject, Row,2) as [Subject 2],
FirstSortedValue(SubjectStatus, Row,2) as [SubjectStatus 2]
Group By Name,ID,Email
;
load recno() as Row ,* inline [
Name,ID,Email,CountofSubject,Location,Subject,SubjectStatus
Xavier,123,xavier@xyz.com,1,Germany,German,Completed 
Xavier,123,xavier@xyz.com,1,France,French,Completed 
Monica,675,monica@xyz.com,1,Spain,Spanish,In Progress
Monica,675,monica@xyz.com,1,England,English,Completed
Ana,456,ana@xyz.com,1,Germany,German,In Progress
];

[sunny_talwar]

For dynamic code to take care of multiple Locations, Subject, SubjectStatus.. you can try this also

Data:
LOAD * INLINE [
    Name, ID, Email, CountofSubject, Location, Subject, SubjectStatus
    Xavier, 123, xavier@xyz.com, 1, Germany, German, Completed
    Xavier, 123, xavier@xyz.com, 1, France, French, Completed
    Monica, 675, monica@xyz.com, 1, Spain, Spanish, In Progress
    Monica, 675, monica@xyz.com, 1, England, English, Completed
    Ana, 456, ana@xyz.com, 1, Germany, German, In Progress
];

Data2:
LOAD *,
	 If(ID = Previous(ID), RangeSum(Peek('Rank'), 1), 1) as Rank
Resident Data
Order by ID;

DROP Table Data;

MaxCount:
LOAD Max(Count) as MaxCount;
LOAD ID,
	 Count(ID) as Count
Resident Data2
Group By ID;

LET vMaxCount = Peek('MaxCount');
DROP Table MaxCount;

FinalData:
LOAD Name,
	 ID,
	 Email,
	 Sum(CountofSubject) as CountofSubject
Resident Data2
Group By Name, ID, Email;

FOR i = 1 to vMaxCount

	Left Join (FinalData)
	LOAD Name,
	 	 ID,
	 	 Email,
	 	 Location as [Location$(i)],
	 	 Subject as [Subject$(i)],
	 	 SubjectStatus as [SubjectStatus$(i)]
	Resident Data2
	Where Rank = $(i);

NEXT

DROP Table Data2;

[Brett_Bleess]

Lavanya, did Vegar's or Sunny's posts help you get what you needed?  If so, please be sure to revisit the thread and use the Accept as Solution button on the post(s) that helped you get things working.  This gives credit to the poster and lets other Community Members know what actually worked for them.  If you are still working on things, let us know what you still need.

Regards,
Brett

[Saravanan_Desingh]

One solution is.

tab1:
LOAD *, AutoNumber(RowNo(),Name) As K1
;
LOAD * INLINE [
    Name, ID, Email, CountofSubject, Location, Subject, SubjectStatus
    Xavier, 123, xavier@xyz.com, 1, Germany, German, Completed
    Xavier, 123, xavier@xyz.com, 1, France, French, Completed
    Monica, 675, monica@xyz.com, 1, Spain, Spanish, In Progress
    Monica, 675, monica@xyz.com, 1, England, English, Completed
    Ana, 456, ana@xyz.com, 1, Germany, German, In Progress
];

Gen:
Generic
LOAD Name, ID, 'Location'&K1, Location
Resident tab1;

Gen:
Generic
LOAD Name, ID, 'Subject'&K1, Subject
Resident tab1;

Gen:
Generic
LOAD Name, ID, 'SubjectStatus'&K1, SubjectStatus
Resident tab1;

FOR i = NoOfTables()-1 to 0 STEP -1
  LET vTable=TableName($(i));
  IF WildMatch('$(vTable)', 'Gen.*') THEN
  LEFT JOIN (tab1) LOAD * RESIDENT [$(vTable)];
  DROP TABLE [$(vTable)];
  ENDIF
NEXT i

[Saravanan_Desingh]