topic Re: Distinct Count Calculation in Script level in QlikView

Distinct Count Calculation in Script level

madhubabum — Wed, 02 Mar 2016 07:40:19 GMT

Hi Experts,

I am having the following scenario as per my requirement (Move "Distinct Count " calculation to script level)

Dataset :

TransactionsID	ProductID	Pname
1	100	a
2	101	b
3	102	c
4	103	d
5	104	e
2	105	f
3	106	g

Note : Calculate Distinct Transactions in Script level

In UI Level : Count(Distinct TransactionsID) = 5,

But Script level calculated field : Count(Dist_TransactionsID) = 7

Here i am attaching the application, excel file..

How we can sort out this issue ,......any help ?

Thanks,

Madhu

Re: Distinct Count Calculation in Script level

Digvijay_Singh — Wed, 02 Mar 2016 07:48:43 GMT

Not sure how you want to write script but below script generates count as 5 -

Temp:

LOAD TransactionsID,

ProductID,

Pname

FROM

Test1.xlsx

(ooxml, embedded labels, table is Sheet1);

Distinct:

Load

Count(DISTINCT TransactionsID) as TransCount

Resident Temp

Re: Distinct Count Calculation in Script level

madhubabum — Wed, 02 Mar 2016 08:04:38 GMT

Hi Digvijay Singh,

Thanks for ur reply..

But in my scenario, I want to concatenate or join this field to Temp Table...

How we can acheive.

Re: Distinct Count Calculation in Script level

Chanty4u — Wed, 02 Mar 2016 08:11:48 GMT

Temp:

LOAD TransactionsID,

ProductID,

Pname

FROM

Test1.xlsx

(ooxml, embedded labels, table is Sheet1);

Join or Concatenate

Distinct:

Load

Count(DISTINCT TransactionsID) as TransCount

Resident Temp

Re: Distinct Count Calculation in Script level

Anonymous — Wed, 02 Mar 2016 08:16:38 GMT

may be like this you can try?

Temp:

LOAD TransactionsID,

ProductID,

Pname

FROM

Test1.xlsx

(ooxml, embedded labels, table is Sheet1);

Let VCount=count(Distinct TransactionsID);

then use this variable at UI?

Re: Distinct Count Calculation in Script level

madhubabum — Wed, 02 Mar 2016 08:17:27 GMT

Hi Chanty,

Thanks for ur reply ....

can u please suggest to use "Join"

Note : I am already used left join, But it won't work

Re: Distinct Count Calculation in Script level

Digvijay_Singh — Wed, 02 Mar 2016 08:21:43 GMT

You can use left Join(Temp) in between but it depends how do you want to see the output - I am not able to understand how you would like to use this count in charts.

Re: Distinct Count Calculation in Script level

Chanty4u — Wed, 02 Mar 2016 08:23:32 GMT

what do u want the exact output?

Re: Distinct Count Calculation in Script level

jagan — Wed, 02 Mar 2016 08:25:31 GMT

Hi,

Are you couning Distinct TransactionID or TransactionID Count?

For Distinct:

Count(Distinct TransactionsID) // Returns 5

Count(TransactionID) // Will give 7

Regards,

Jagan.

Re: Distinct Count Calculation in Script level

PrashantSangle — Wed, 02 Mar 2016 08:29:18 GMT

Hi,

can you post your script??

Regards

Re: Distinct Count Calculation in Script level

madhubabum — Wed, 02 Mar 2016 08:32:00 GMT

Hi Jagan Mohan Rao Appala,

Thanks for your reply...

Here I want to calculate Count(Distinct TransactionsID) in Script level (I want to use this field in UI Level)

Note :

1 ) For counting Distinct Transactions it will take long time (That's why i want to move this calculation to script level)

2) I want to join / Concatenate this field to Main table

Any suggestions ?

Thanks,

Madhu

Re: Distinct Count Calculation in Script level

madhubabum — Wed, 02 Mar 2016 08:35:25 GMT

Test:

LOAD TransactionsID,

ProductID,

Pname

FROM

(ooxml, embedded labels, table is Sheet1);

left Join(Test)

Load

TransactionsID ,

Count(Distinct TransactionsID) as Dist_TransactionsID Resident Test Group by TransactionsID;

Re: Distinct Count Calculation in Script level

PrashantSangle — Wed, 02 Mar 2016 08:36:33 GMT

Hi;

Like this

Temp:

LOAD * inline [

TransactionsID, ProductID, Pname

1, 100, a

2, 101, b

3, 102, c

4, 103, d

5, 104, e

2, 105, f

3, 106, g

];

join

Load Count(Distinct TransactionsID) as count_transaction Resident Temp;

It is giving count 5

Regards

Re: Distinct Count Calculation in Script level

jagan — Wed, 02 Mar 2016 08:48:09 GMT

Hi,

Try like this

Temp:

LOAD TransactionsID,

ProductID,

Pname

FROM

Test1.xlsx

(ooxml, embedded labels, table is Sheet1);

DistinctTransactions:

Load DISTINCT

TransactionID,

1 as TransCount

Resident Temp;

Now use Sum(TransCount) in frontend.

Hope this helps you.

Regards,

jagan.

Re: Distinct Count Calculation in Script level

Anonymous — Wed, 02 Mar 2016 09:38:41 GMT

try like this?

Test:

LOAD TransactionsID,

autonumber(TransactionsID) as Count,

ProductID,

Pname

FROM

(ooxml, embedded labels, table is Sheet1);

Let VCount=peek('Count',-1,'Test');

Now use $(VCount) at UI.

Hope this will help!!

Re: Distinct Count Calculation in Script level

madhubabum — Wed, 02 Mar 2016 10:34:15 GMT

Hi Jagan Mohan Rao,

This logic is working fine.

Thank you very much.....

But i am using Count(TransCount) instead of Sum(TransCount) ......Now also i am getting the same results.

Note : Can i use Count() instead of SUM()

Madhu

Re: Distinct Count Calculation in Script level

Anonymous — Wed, 02 Mar 2016 10:43:16 GMT

Try and See

it should work!!

dont Use Distinct with Count

Re: Distinct Count Calculation in Script level

puttemans — Wed, 02 Mar 2016 10:43:35 GMT

Hi there,

Maybe this will suit you?

Temp:

LOAD TransactionsID,

ProductID,

Pname

FROM

Test1.xlsx

(ooxml, embedded labels, table is Sheet1);

Map_Distinct:

MAPPING Load

TransactionsID,

Count(DISTINCT TransactionsID) as TransCount

Resident Temp

Temp2:

LOAD *,

Applymap ('Map_Distinct', TransactionsID) as TransCount

RESIDENT Temp;

DROP TABLE Temp;

Regards,

johan

Re: Distinct Count Calculation in Script level

jagan — Thu, 03 Mar 2016 00:51:57 GMT

Hi,

Ok. Both are same, since there is 1 in all the records.

Regards,

Jagan.