topic My sql query to qlikview script in QlikView

My sql query to qlikview script

balasundaram — Mon, 03 Nov 2014 08:39:30 GMT

My SQL Query: select A,B,count(1) from bala;

i written in qlikview like

Load A,B,count(1) from bala.qvd;

is it correct ?

any function there in qlikview for my sql COUNT function

Re: My sql query to qlikview script

anbu1984 — Mon, 03 Nov 2014 08:44:20 GMT

You can use Count(1) in QV

Load A,B,count(1) from bala.qvd Group by A,B;

Re: My sql query to qlikview script

aveeeeeee7en — Mon, 03 Nov 2014 08:45:57 GMT

Count() function is available in Qlikview

Since, its an Aggregate function so you need to use Group By clause.

eg.

Main_Table:

Load

A, B

Count(Distinct Value) AS New_Value

From Table.......

GROUP BY A,B;

Regards

Av7eN

Re: My sql query to qlikview script

its_anandrjs — Mon, 03 Nov 2014 08:47:57 GMT

Hi,

What out out get when using mysql query that is select A,B,count(1) from bala; let me knwo

And in Qlikview you can use same but need to use Group By by all dimension in table

Load

A,B,

count(1) from bala

Group By A,B;

Regards

Anand

Re: My sql query to qlikview script

jagan — Mon, 03 Nov 2014 08:50:47 GMT

Hi,

Try like this

Load A,B,

count(A) AS Count

from bala.qvd

Group by A,B;

Regards,

jagan.

Re: My sql query to qlikview script

balasundaram — Mon, 03 Nov 2014 09:06:19 GMT

bala:

LOAD * INLINE [

sno, name

1, a

2, b

3, c

4, d

5, e

];

LOAD sno,name,count(sno)

Resident bala Group by sno,name;

drop table bala;

in qlikview iam getting count '1'

but in my sql count '5'

i have tried like count(1),count(name),count(sno),count(sno&name)...but same result in qlikview '1'

now check and tell me...

Re: My sql query to qlikview script

balasundaram — Mon, 03 Nov 2014 09:06:52 GMT

bala:

LOAD * INLINE [

sno, name

1, a

2, b

3, c

4, d

5, e

];

LOAD sno,name,count(sno)

Resident bala Group by sno,name;

drop table bala;

in qlikview iam getting count '1'

but in my sql count '5'

i have tried like count(1),count(name),count(sno),count(sno&name)...but same result in qlikview '1'

now check and tell me...

Re: My sql query to qlikview script

anbu1984 — Mon, 03 Nov 2014 09:08:09 GMT

If Nulls are present in A, then Count() ignores that row

Set NullInterpret='';

Load A,B,Count(1),Count(A) Group by A,B;

Load * Inline [

A,B

1,1

1,2

,3 ];

Re: My sql query to qlikview script

anbu1984 — Mon, 03 Nov 2014 09:12:54 GMT

Do you have duplicates in Mysql table?

Re: My sql query to qlikview script

Mon, 03 Nov 2014 10:29:32 GMT

It will definitely give you '1' as the output, since you are grouping it by sno and name, which will uniquely count each row in this case since you do not have any duplicates in this.

You have to use SUM up this count in Front End charts to show or in the script create a separate table for counts:

counttable:

LOAD count(sno) as cnt

Resident bala;

Hope this was helpful.

Thanks,

Singh

Re: My sql query to qlikview script

jagan — Tue, 04 Nov 2014 01:13:10 GMT

Hi,

You will definitely get one, since your table has all unique values

bala:

LOAD * INLINE [

sno, name

1, a

2, b

3, c

4, d

5, e

];

If you have data like this

Temp:

LOAD * INLINE [

sno, name

1, a -- duplicate record

1, c

2, d

2, e

];

Data:

LOAD sno,name,count(sno) AS Count

Resident TempGroup by sno,name;

drop table Temp;

Now you will get

sno, name, count

1, a, 2 -- since you have duplicate rows you will get count 2.

1, c, 1

2, d, 1

2, e, 1

Bala, before practicing Qlikview first learn the SQL queries then designing the Datamodel in Qlikview becomes very easy. Without knowing SQL it is very difficult for a Qlikview developer.

Hope this helps you.

Regards,

Jagan.