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

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

Lavanya — Sat, 16 Nov 2024 19:39:23 GMT

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!

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

Vegar — Mon, 18 Nov 2019 10:19:15 GMT

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

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

sunny_talwar — Mon, 18 Nov 2019 12:02:55 GMT

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;

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

Brett_Bleess — Wed, 20 Nov 2019 17:42:56 GMT

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

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

Saravanan_Desingh — Fri, 22 May 2020 01:00:16 GMT

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

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

Saravanan_Desingh — Fri, 22 May 2020 01:01:18 GMT