perform a sql group in Qlikview or on database via sql

[josephinetedesc]

Hi

to perform a sql group - does not work in  Qlikview - so should I do it on the database via SQL or maybe get a QVD file in the first instance???

Jo

[robert_mika]

Could you specify your question?

[josephinetedesc]

Hi Robert

I am not a Power user of SQL so ..

I looked at the data retrieved from the SQL call in Access.

I made a query of the data received - then I tried to substitute the query (got rid of the table names) into QlkView:

it came back as an error (it does not support Group by???)

so - I suppose I have to do that bit in SQL?

Then i looked at creating a QVD file (and I am now in the middle of trying to see if it will do the grouping, but I suspect I will need to do the grouping on SQL.  But the big advantage of the QVD file is that I do not have to make a call on the live server to check what I am doing.

Does that make sense?

Jo

[Anonymous]

That's fine to use QVD files, but "group by" works in QlikView for sure.  You probably did something not quite right (?)

[josephinetedesc]

  THis is my script:

LOAD [rowNo()],
App_DtTm2,
DateFull,
WeekNum,
usegraphDay,
App_DtTm,
Machine,
Pat_ID1,
Activity,
Short_Desc,
stsNumber,
Min(stsNumber) AS MinOfstsNumber,
Min(Test) AS MinOfTest,
Inst_ID,
[machine number],
PatName,
URNum,
PatSts,
DurTime,
HrDur,
MinDur,
DurMins,
Sts,
AxtivityType,
ActivitySts,
StatusGiven,
Clinic,
Test

FROM

(qvd)

GROUP BY App_DtTm, App_DtTm2, Machine, Pat_ID1, Activity, Short_Desc, Inst_ID, PatName, URNum, DurTime, Sts, Clinic;
;
this is the error

Invalid expression
LOAD [rowNo()],
     App_DtTm2,
     DateFull,
     WeekNum,
     usegraphDay,
     App_DtTm,
     Machine,
     Pat_ID1,
     Activity,
     Short_Desc,
     stsNumber,
     Min(stsNumber) AS MinOfstsNumber,
     Min(Test) AS MinOfTest,
     Inst_ID,
     [machine number],
     PatName,
     URNum,
     PatSts,
     DurTime,
     HrDur,
     MinDur,
     DurMins,
     Sts,
     AxtivityType,
     ActivitySts,
     StatusGiven,
     Clinic,
     Test
    
FROM

(qvd)

GROUP BY App_DtTm, App_DtTm2, Machine, Pat_ID1, Activity, Short_Desc, Inst_ID, PatName, URNum, DurTime, Sts, Clinic

[josephinetedesc]

new error says Aggregation expressions required by GROUP BY clause

so obviously I am doing something wrong

[jagan]

Hi,

I think you missed some columns in the Group by clause which you used in Load statement.  You have to specify all the columns in the group by clause. Try below script

LOAD

  *,

  RowNo() AS RowNum;

LOAD

App_DtTm2,

DateFull,

WeekNum,

usegraphDay,

App_DtTm,

Machine,

Pat_ID1,

Activity,

Short_Desc,

stsNumber,

Inst_ID,

[machine number],

PatName,

URNum,

PatSts,

DurTime,

HrDur,

MinDur,

DurMins,

Sts,

AxtivityType,

ActivitySts,

StatusGiven,

Clinic,

Test,

Min(stsNumber) AS MinOfstsNumber,

Min(Test) AS MinOfTest

FROM

(qvd)

GROUP BY App_DtTm2,

DateFull,

WeekNum,

usegraphDay,

App_DtTm,

Machine,

Pat_ID1,

Activity,

Short_Desc,

stsNumber,

Inst_ID,

[machine number],

PatName,

URNum,

PatSts,

DurTime,

HrDur,

MinDur,

DurMins,

Sts,

AxtivityType,

ActivitySts,

StatusGiven,

Clinic,

Test;

Hope it helps you.

Regards,

jagan.

[josephinetedesc · Accepted solution]

Thank you that certainly works Thank you, Thank you, Thank you!

- for best results in terms of timing and maintenance - would you store the qvd file on the qlikview server?

So the daily sequence would be:

1.     Qlkview (file 1) server calls the SQL function - refreshed data loaded into QlikView.

2.     Make the qvd table

3.     have Qlkview file 2 use the qvd table and produce the graphs and tables etc

Or

Use the grouping on SQL - I think I wold do this when I have finished "tweaking" ... that way i do not have to keep running from the live database???

Jagan I will do the Mark as correct Answer when I find the button

[jagan]

Hi,

Generally it is a good practice to load the data from SQL and save it in QVD, so that we don't need to hit database multiple times, if we have multiple dashboards which uses the same data then we can directly use the QVD data.  This way we can reduce the load on database also load from QVD is 100X faster than that of database.

Generally the steps is

1. Fetch data from source (Database/Flat files)

2. Store it in QVD

3. Consume data in QVD in Qlikview file.

Grouping on SQL or in Qlikview script totally depends on your requirement, but doing this in Qlikview scripting is the best option.

Regards,

Jagan.

[jagan]

Hi,

Instead of your post you would have marked the post which is helpful to you in finding the solution. 

Regards,

Jagan.