How to write sql query in EditScript ??

[nikhilgarg]

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

[nikhilgarg]

Hey,

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

[alex_millan]

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.

[nikhilgarg]

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

[maleksafa]

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.

[alex_millan]

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

[anbu1984]

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;

[nikhilgarg]

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

[nikhilgarg]

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>

[nikhilgarg]

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

[nikhilgarg]

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