topic Group By on Excel file in QlikView

Group By on Excel file

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Group By on Excel file

parthesh — Thu, 06 Dec 2018 14:56:02 GMT

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.

Re: Group By on Excel file

Mark_Little — Thu, 06 Dec 2018 15:03:17 GMT

Re: Group By on Excel file

Mark_Little — Thu, 06 Dec 2018 15:04:37 GMT

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

Re: Group By on Excel file

Anonymous — Thu, 06 Dec 2018 15:26:48 GMT

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.

Re: Group By on Excel file

Anonymous — Thu, 06 Dec 2018 15:29:05 GMT

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

Re: Group By on Excel file

Mark_Little — Thu, 06 Dec 2018 15:31:58 GMT

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

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

Re: Group By on Excel file

parthesh — Thu, 06 Dec 2018 15:52:03 GMT

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