Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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.
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
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))