Ok check attached. So things to tweak around at your end. We have two options.
1st Option.
1. Load the qvw file based on your Excel path and i have given CalcDim1, CalcDim2....CalcDim4 as your fieldnames in the excel where i have those long expressions.
2. Then loaded the data into Qv and used aggr function in Qlikview straight table. Check straight table.
2nd Option
is just Remove the Exit Script from the script you see in the QVW file and if you reload it will just load only those rows which you want to see. So your choice.
Full Script:
Option1:
Curriculum:
LOAD *, RowNo() AS RowNum,[Event Curriculum ID]&[Curriculum Name] AS KeyCourseID ;
LOAD [Site Participant Full Name],
[Curriculum Name],
[Event Source Name],
[Event Speaker Bureau ID],
[Speaker Bureau Name],
[Vendor Event ID],
Date([Event Start Date],'MM.DD.YYYY') AS EventStartDate,
[Event Status],
[Event Curriculum ID],
[Site Participant SFDC ID],
CREDENTIAL,
[Site Participant Profession],
[Event Type],
[Date Trained],
[Training Record ID],
[Site Participant Attendance Status],
[PRC ID],
CalcDim1,
CalcDim2,
CalcDim3,
CalcDim4
FROM
[..\..\..\Mockup data.xls]
(biff, embedded labels, table is [Mockup data$])
;
Option 2:
Curriculum:
LOAD *, RowNo() AS RowNum,[Event Curriculum ID]&[Curriculum Name] AS KeyCourseID ;
LOAD [Site Participant Full Name],
[Curriculum Name],
[Event Source Name],
[Event Speaker Bureau ID],
[Speaker Bureau Name],
[Vendor Event ID],
Date([Event Start Date],'MM.DD.YYYY') AS EventStartDate,
[Event Status],
[Event Curriculum ID],
[Site Participant SFDC ID],
CREDENTIAL,
[Site Participant Profession],
[Event Type],
[Date Trained],
[Training Record ID],
[Site Participant Attendance Status],
[PRC ID],
CalcDim1,
CalcDim2,
CalcDim3,
CalcDim4
FROM
[..\..\..\Mockup data.xls]
(biff, embedded labels, table is [Mockup data$])
;
INNER JOIN
Final:
Load *
Where RowCount > 1;
LOAD KeyCourseID, Count(RowNum) AS RowCount
Resident Curriculum
Group By KeyCourseID;
