topic Re: Creating a key and counting in QlikView

Creating a key and counting

Anonymous — Tue, 19 Jan 2016 23:57:10 GMT

I am trying to create a key based on the following fields:

I need a key based on the [Script Number] and Branch. Where the [Script Number] and the Branch make a unique key, this should generate the same key for example [Script Number] 102356 and Branch [First] should generate the same key for the same combination. I would then want a count of this key, something like the following:

[Distinct Script Data]:
BUFFER LOAD [Script Number],
AutoNumber([Script Number], [Branch]) as Script#,
Count Script# as Rx
Group by [Script Number];
SQL SELECT DISTINCT
"Script Number"
FROM PharmaMarketAudit.dbo.tblDatawarehouseConsolidatedCleanStage
WHERE [Date Dispensed] >= '2012-01-01' and [Date Dispensed] <= '2015-08-31';

How can this be accomplished?

Regards

Re: Creating a key and counting

jagan — Wed, 20 Jan 2016 01:32:13 GMT

HI,

Try like this

[Distinct Script Data]:
BUFFER LOAD [Script Number],
AutoNumber([Script Number] & [Branch]) as Script#,
Count Script# as Rx
Group by [Script Number];
SQL SELECT DISTINCT
"Script Number"
FROM PharmaMarketAudit.dbo.tblDatawarehouseConsolidatedCleanStage
WHERE [Date Dispensed] >= '2012-01-01' and [Date Dispensed] <= '2015-08-31';

Regards,

Jagan.

Re: Creating a key and counting

Anonymous — Wed, 20 Jan 2016 04:49:12 GMT

Thanks Jagan, i will try it out and advise accordingly.

Regards.