Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Group By on Excel file

Hello,

My Qlikview document loads in an Excel table similar to the one below:

 

LOAD
Rec_Ref,
Rep_Date,
[Audit Recommendations],
Rec_Ranking,
Systemic,
[Audit Report Name],
Division,
[Function Responsible],
[Location / Region],
[Assistant Head Responsible],
Email,
[Report Owner],
Email1,
[Contact Tel. No.],
Status,
[Date Rec Updated],
[Update on Recommendation],
[Update on Report],
[Update on Year],
[Movement from previous IA Report],
[Report to be Re-assigned],
[Recommendation with another Division],
[Report included in Internal Audit Division Report],
[Type of Audit Report],
CompTot,
If([Rec_Ranking]='H','1.H',if([Rec_Ranking]='M','2.M','3.L')) as [Rec_Rank_Ref],
if([Status]<>'Implemented/Superceded',1,0) as [O/S Rec Num],
if([Status]<>'Implemented/Superceded','Y','N') as [O/S Rec]

FROM
[audit recs.xlsx]
(ooxml, embedded labels, table is [Compdatabase]);

 

I was looking to add the following to the script:

LOAD
Status,
SUM(CompTot) as CompTot
GROUP By CompTot;

SQL SELECT Status,

CompTot
[audit recs.xlsx]
(ooxml, embedded labels, table is [Compdatabase]);

 

But it was giving me a Syntax error related to the SQL Select (I think).

Why would this be or where am I going wrong?

Thank you for reading.

7 Replies
parthesh
Creator
Creator

i think you load statement is syntactically wrong, if you want to load using SQL you have to something like below.

 

xx_abc:
LOAD *;
SQL SELECT *
    FROM "xx_test";

 

i am not sure what you are trying to achieve by introducing new code but if you can describe in detail than it will help me to resolve this.

or if you can upload sample qvw and excel than also fine.

Mark_Little
Luminary
Luminary

 
Mark_Little
Luminary
Luminary

Hi,

just resident the tablw you just loaded, should be quicker.
LOAD
Status,
SUM(CompTot) as CompTot
Resident Table
GROUP By CompTot;

Table being the name you have given the first load.

Mark
Anonymous
Not applicable
Author

Thanks, Parthesh.

Its hard to describe but I'll try.

My table has 400 total records.

200 are category 'open'

150 'closed'

50 'In Progress'

Ultimately I want to show these 3 KPIs (possibly) as a Text Object in the dashboard.

But I also need the '400' to be unaffected by Current Selections.

Therefore I was going to use the CompTot field to sum to 400. Assign that to every record then get an Average of all the records which will mean it will always remain as 400 no matter the Current Selection.

 

Anonymous
Not applicable
Author

Thanks, Mark6505.

For some reason this is giving me an 'Invalid Expression' error.

Called my initial Load 'Audit1:'

Changed your 'Resident Table' to Resident Audit1

 

Mark_Little
Luminary
Luminary

Hi,
Probably down to a few things try the below.

Noconcatenate
Table:
LOAD
Status,
SUM(CompTot) as CompTot2
Resident Audit1
GROUP By CompTot;
parthesh
Creator
Creator

i am not sure.. but by seeing your requirement i think this can be handled by expression it self.

you just have to put

=Count({<category={'Open'}>}(record_id))

 

=Count({<category={'Closed'}>}(record_id))

 

=Count({<category={'In Progress'}>}(record_id))

 

and for all 400 record count you can put

 

=Count({1}(record_id))