topic Re: perform a sql group in Qlikview or on database via sql in QlikView

perform a sql group in Qlikview or on database via sql

josephinetedesc — Wed, 17 Jun 2015 00:14:10 GMT

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

Re: perform a sql group in Qlikview or on database via sql

robert_mika — Wed, 17 Jun 2015 01:44:45 GMT

Could you specify your question?

Re: perform a sql group in Qlikview or on database via sql

josephinetedesc — Wed, 17 Jun 2015 01:51:53 GMT

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?

Re: perform a sql group in Qlikview or on database via sql

Anonymous — Wed, 17 Jun 2015 01:58:22 GMT

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

Re: perform a sql group in Qlikview or on database via sql

josephinetedesc — Wed, 17 Jun 2015 02:22:55 GMT

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

Re: perform a sql group in Qlikview or on database via sql

josephinetedesc — Wed, 17 Jun 2015 02:31:45 GMT

new error says Aggregation expressions required by GROUP BY clause

so obviously I am doing something wrong

Re: perform a sql group in Qlikview or on database via sql

jagan — Wed, 17 Jun 2015 02:40:59 GMT

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.

Re: perform a sql group in Qlikview or on database via sql

josephinetedesc — Wed, 17 Jun 2015 02:55:52 GMT

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

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

Re: perform a sql group in Qlikview or on database via sql

jagan — Wed, 17 Jun 2015 03:03:45 GMT

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.

Re: perform a sql group in Qlikview or on database via sql

jagan — Wed, 17 Jun 2015 04:34:27 GMT

Hi,

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

Regards,

Jagan.