Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
MatBlack
New Contributor II

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
Contributor

Re: Group By on Excel file

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.

mark6505
Valued Contributor III

Re: Group By on Excel file

 
mark6505
Valued Contributor III

Re: Group By on Excel file

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
MatBlack
New Contributor II

Re: Group By on Excel file

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.

 

MatBlack
New Contributor II

Re: Group By on Excel file

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

 

mark6505
Valued Contributor III

Re: Group By on Excel file

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

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

Re: Group By on Excel file

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))

 

 

Community Browser