Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

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

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!

5 Replies
Highlighted
Partner
Partner

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

 

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Highlighted

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;
Highlighted
Digital Support
Digital Support

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
Highlighted
Specialist III
Specialist III

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
Highlighted
Specialist III
Specialist III

commQV65.PNG