topic Re: How to get the correct Rowno in QlikView

How to get the correct Rowno

jolivares — Fri, 18 Sep 2015 21:13:42 GMT

Hi Every one, here you can find out a piece of a chart showing a total qty and other column with the accumulation, everything it's ok, but the order of de RowNo() is incorrect attending to the order. Let's see

Can someone see the attached qvw in order to get the correct solution?... Thamks.

gwassenaar‌

Re: How to get the correct Rowno

marcus_sommer — Sat, 19 Sep 2015 08:54:51 GMT

Sorting will be by design disabled if inter-record functions are used, see: Re: Interactive sort not working when using RowNo() for coloring alternative rows in straight table.

- Marcus

Re: How to get the correct Rowno

jolivares — Tue, 22 Sep 2015 21:42:25 GMT

I already have the correct rowno but no the expected result accumulating the QTY in the correct order.

Re: How to get the correct Rowno

petter — Wed, 23 Sep 2015 04:38:37 GMT

I am sorry but I don't catch what your problem is. Could you give us more details?

Is it interactive sort or is it how the calculations are ordered or carried out or is it in the sums you are getting.

If you could give us an example of what is wrong and what should be the right...

Re: How to get the correct Rowno

hic — Wed, 23 Sep 2015 06:57:57 GMT

If you want the accumulated number of Sum(QTY), you should get rid of all the Aggr() functions and instead use

RangeSum(Above(total Sum(QTY),0,RowNo(total)))

HIC

Re: How to get the correct Rowno

jolivares — Wed, 23 Sep 2015 15:10:32 GMT

Thanks for your question. I'm not worried about the interactive sort, the problem is the order of the accumulation.

Feel free and see the new file attached and inside I explain better the require.

Re: How to get the correct Rowno

Anonymous — Thu, 24 Sep 2015 08:21:36 GMT

Hi Henric,

am I able to add a question to you directly?

Laura

Re: How to get the correct Rowno

hic — Fri, 25 Sep 2015 18:09:12 GMT

Just send me a message here on QlikCommunity

HIC

Re: How to get the correct Rowno

jolivares — Fri, 25 Sep 2015 18:40:56 GMT

May you ckeck the last post of the file, the last answer you gave me no does not work.

Or it something that QV no mange? Exist a workaround to solve it?

Thanks.

Re: How to get the correct Rowno

Anonymous — Tue, 29 Sep 2015 08:10:05 GMT

Hi Henric,

I am still struggling to understand why I am getting duplicates and how I can overcome this issue.

I have trouble shooted this in SQL and I found the duplication of records (very few) occurs due to the link between the “training_contracts” and “training_contract_returns” tables.When I remove “training_contract_returns” from the sql, there are no duplications.

I think the issue is this……there is only one field in the “training_contract_returns” (id), that I can use to link to other tables:-

How can I tackle this situation?

Laura Castagna

Business Systems and Process Analyst, NSW Business Chamber

140 Arthur Street North Sydney NSW 2060

Tel: 02 9458 7804 | Mob: 0429 486 934 | Web: www.nswbusinesschamber.com.au<http://www.nswbusinesschamber.com.au>

Twitter<http://www.twitter.com/nswbc> | Facebook<https://www.facebook.com/NSWBusinessChamber> | LinkedIn<http://www.linkedin.com/company/388425?trk=saber_s000001e_1000> | YouTube<http://www.youtube.com/nswbctv>

<http://www.nswbusinesschamber.com.au/>

Proud supporter of:

<http://acci.headsup.org.au/>

Re: How to get the correct Rowno

Anonymous — Tue, 29 Sep 2015 08:24:29 GMT

p.s. I understand this is a SQL issue but is there a way to handle this in QlikView.

Laura Castagna

Business Systems and Process Analyst, NSW Business Chamber

140 Arthur Street North Sydney NSW 2060

Tel: 02 9458 7804 | Mob: 0429 486 934 | Web: www.nswbusinesschamber.com.au<http://www.nswbusinesschamber.com.au>

<http://www.nswbusinesschamber.com.au/>

Proud supporter of:

<http://acci.headsup.org.au/>

Re: How to get the correct Rowno

hic — Tue, 29 Sep 2015 08:35:45 GMT

Remove the Join from the script. Then the calculations will (probably) be correct.

HIC

Re: How to get the correct Rowno

Anonymous — Tue, 29 Sep 2015 08:38:52 GMT

I cant as I need a field from that table. the "at_fault" field.

Re: How to get the correct Rowno

flipside — Tue, 29 Sep 2015 08:41:33 GMT

What are you trying to return from the training_contract_returns table? That table must contain multiple entries for the training_contract_id value which is why duplicates are being returned. You need to decide which of these multiple values you need (max, min, first, last etc)..

flipside

Re: How to get the correct Rowno

Anonymous — Tue, 29 Sep 2015 08:51:06 GMT

could well duplicated in the TrainingReturns table. its a tricky table to query as there are no parameters for me to use to check for duplications! I'd have to go through each consultant which would take me forever.

I only need a count of contracts so I can take the first or second.

Re: How to get the correct Rowno

Anonymous — Tue, 29 Sep 2015 09:30:21 GMT

I'm trying to return the "contract" count so it doesn't matter if the first or second is counted. I've tried this without success but maybe its bc i dont know how......is there a way of counting "distinct" rows based on a field in QlikView.

For example, can i count a field with a Distinct function in the expression OR in the Load?

Re: How to get the correct Rowno

hic — Tue, 29 Sep 2015 20:52:50 GMT

If you need a field from an other table, you could use Applymap(). See Don't join - use Applymap instead.

HIC

Re: How to get the correct Rowno

flipside — Wed, 30 Sep 2015 08:37:45 GMT

Re: How to get the correct Rowno

jolivares — Wed, 30 Sep 2015 12:20:04 GMT

Thanks for your comments, just think that muy question is not answer yet and every day I receive an update about this case that is not my case.

Re: How to get the correct Rowno

flipside — Wed, 30 Sep 2015 12:44:06 GMT