topic Re: How to write sql query in EditScript ?? in QlikView

How to write sql query in EditScript ??

nikhilgarg — Tue, 03 Mar 2015 14:51:22 GMT

Hey,

I have 3 columns say: Item1 , Item2 , Item3. Now i have to count their numbers by writing SqlQueries.

In expression i have used Count(Item1Id), COunt(Item2Id) , Count(Item3Id).

In Script window how can i write the same in Sql query form?? Because if i write:

SQL Select count(Item1ID) from abc ; but what do i write in LOAD statement above it??

tHanks

Re: How to write sql query in EditScript ??

alex_millan — Tue, 03 Mar 2015 14:54:36 GMT

LOAD 'Item1' As Item, NCount;

SQL SQL Select count(Item1ID) As NCount from abc;

Re: How to write sql query in EditScript ??

giakoum — Tue, 03 Mar 2015 15:00:22 GMT

one SQL too many

Re: How to write sql query in EditScript ??

nikhilgarg — Tue, 03 Mar 2015 15:00:53 GMT

Hey Thankx but i have to select all coulmns and have to count as well.

Can i write:

SQL Select * , Cout(Item1) As NCount from abc;

Re: How to write sql query in EditScript ??

alex_millan — Tue, 03 Mar 2015 15:02:30 GMT

LOAD *; SQL SELECT...

😉

Re: How to write sql query in EditScript ??

alex_millan — Tue, 03 Mar 2015 15:04:17 GMT

LOL, You know Ioannis, as always... two better than one! XD (Just joking!)

Re: How to write sql query in EditScript ??

giakoum — Tue, 03 Mar 2015 15:05:19 GMT

will not work unless you specify a group by clause as well

Re: How to write sql query in EditScript ??

Tue, 03 Mar 2015 15:08:15 GMT

I do not believe you can as Count is a Aggragation function:

However you can do the following:

Temp:

Select

Item1 ,

Item1id,

Item2 ,

Item2id

From abc;

Count:

Load

Item1 ,

Count(Item1id)

Resident Temp

Group by Item1;

Regards

Re: How to write sql query in EditScript ??

anbu1984 — Tue, 03 Mar 2015 15:08:58 GMT

Which database are you using?

SQL Select * , Count(Item1) Over() As NCount from abc;

SQL Select * , (Select Count(Item1) From abc) As NCount from abc;

Re: How to write sql query in EditScript ??

nikhilgarg — Tue, 03 Mar 2015 15:21:44 GMT

Hey,

It is giving me error

Re: How to write sql query in EditScript ??

nikhilgarg — Tue, 03 Mar 2015 15:25:09 GMT

Hey,

Thanx first option works. But what does Over() means? and I am getting incorrect number of counts

Re: How to write sql query in EditScript ??

alex_millan — Tue, 03 Mar 2015 15:27:30 GMT

Have you tested your SQL sentence?

The prefix "LOAD *;" before your SQL sentence just loads all the fields of your SQL Query to the Qlik table.

Re: How to write sql query in EditScript ??

nikhilgarg — Tue, 03 Mar 2015 15:31:16 GMT

Hey,

I ahve used following:

SQL Select * , Count(Item1) Over() As NCount from abc;

But it is giving me wrong count as compare to same count in pivot table.

Plz Guide

Re: How to write sql query in EditScript ??

maleksafa — Tue, 03 Mar 2015 15:35:31 GMT

count is an aggregation function, therefore it should be followed by a group by.

for example:

select Name, Count(ID) from abc group by Name

it will return the count of ID for each Name.

Re: How to write sql query in EditScript ??

alex_millan — Tue, 03 Mar 2015 15:43:07 GMT

As others members pointed out, if you use an aggregation function as "count" you must use a group by clause.

For example, to load field1, field2, field3, field4, and count field5 you would write

SQL SELECT Field1, Field2, Field3, Field4, Count(Field5) As NCount from ABC group by Field1, Field2, Field3, Field4

Hope that helps,

Regards

Re: How to write sql query in EditScript ??

anbu1984 — Tue, 03 Mar 2015 16:02:37 GMT

Count() Over() - Analytical function which operate on the group of rows defined by the contents of the Over clause

You dont need group by for Analytical function

Count from these two sqls will be same

Select Count(Item1) From abc;

Select Field1,Field2,Count(Item1) Over() From abc;

Re: How to write sql query in EditScript ??

nikhilgarg — Tue, 03 Mar 2015 17:20:09 GMT

Hey ,

Thanx but let me tell you wat i am doing :

1: i have loaded data from database.

2: i have a pivot table and in that i have to show count of item1, item2

and item3.

3: so i used expreesion count(item1) and same for item2 and item3.

4: the count are shown in pivot table as per region wise.

Now,

I want to confirm that same count should come if i execute sql query in

database. But when i executing;

Select count(item1) from.... , it is giving me wrong output as compared to

pivot table expression output. Plz tell wat to do.

Thanks

Re: How to write sql query in EditScript ??

nikhilgarg — Tue, 03 Mar 2015 17:20:45 GMT

Hey ,

Thanx but let me tell you wat i am doing :

1: i have loaded data from database.

2: i have a pivot table and in that i have to show count of item1, item2

and item3.

3: so i used expreesion count(item1) and same for item2 and item3.

4: the count are shown in pivot table as per region wise.

Now,

I want to confirm that same count should come if i execute sql query in

database. But when i executing;

Select count(item1) from.... , it is giving me wrong output as compared to

pivot table expression output. Plz tell wat to do.

Thanks

On 03-Mar-2015 8:35 PM, "ioannis giakoumakis" <qcwebmaster@qlikview.com>

Re: How to write sql query in EditScript ??

nikhilgarg — Tue, 03 Mar 2015 17:21:57 GMT

Hey ,

Thanx but let me tell you wat i am doing :

1: i have loaded data from database.

2: i have a pivot table and in that i have to show count of item1, item2

and item3.

3: so i used expreesion count(item1) and same for item2 and item3.

4: the count are shown in pivot table as per region wise.

Now,

I want to confirm that same count should come if i execute sql query in

database. But when i executing;

Select count(item1) from.... , it is giving me wrong output as compared to

pivot table expression output. Plz tell wat to do.

Thanks

Re: How to write sql query in EditScript ??

nikhilgarg — Tue, 03 Mar 2015 17:23:09 GMT

Hey ,

Thanx but let me tell you wat i am doing :

1: i have loaded data from database.

2: i have a pivot table and in that i have to show count of item1, item2

and item3.

3: so i used expreesion count(item1) and same for item2 and item3.

4: the count are shown in pivot table as per region wise.

Now,

I want to confirm that same count should come if i execute sql query in

database. But when i executing;

Select count(item1) from.... , it is giving me wrong output as compared to

pivot table expression output. Plz tell wat to do.

Thanks