topic Re: Sorting by with two levels in QlikView

Sorting by with two levels

Thu, 14 May 2015 10:43:09 GMT

Hi,

In the Load script on Qlikview, I'm wondering how I can sort by two criteria, so it'd sort by a number first but if the number is the same, it'd order by age. I've been trying to use ORDER BY Main_MEM_NO_TEMP,RENEWAL_AGE; (those are the two variables I want to sort by), but that doesn't seem to be working. Anyone have any ideas

Code I have is

MemPolicy_Temp:

LOAD

JOIN_MEM_NO,

JOIN_MEM_NO as JOIN_MEM_NO_TEMP,

Main_MEM_NO as Main_MEM_NO_TEMP,

RENEWAL_AGE

RESIDENT MemPolicy

where mixmatch(ASC_RENEWAL,'Child',ASC_RENEWAL = 'Student')

order by Main_MEM_NO, RENEWAL_AGE

;

MemPolicy_Child:

LOAD

JOIN_MEM_NO,

If(Main_MEM_NO_TEMP <> Previous(Main_MEM_NO_TEMP), 1,

NumSum(peek(Counter) + 1)) as Counter

RESIDENT MemPolicy_Temp

ORDER BY Main_MEM_NO_TEMP,RENEWAL_AGE;

So, it should have a counter of 1 for the oldest child, and a counter of 2 for the next oldest. However, it's placing the counter on the one with the first membership number

Re: Sorting by with two levels

sunny_talwar — Thu, 14 May 2015 10:55:58 GMT

So the script isn't adding counters to all the children? It is just adding 1 for the oldest child? Is that what the issue is?

Sunny

Re: Sorting by with two levels

john_s_w_wood — Thu, 14 May 2015 10:57:23 GMT

do you have an example of your data please?

Re: Sorting by with two levels

Thu, 14 May 2015 10:59:27 GMT

Nope, it adds counters to all the children, but it adds it based on highest member number instead of oldest child. So, the if a member ends in 2 and is a child, it'll get assigned 1 over a child that ends in 3 even if the one that ends in 3 is the older child.

Re: Sorting by with two levels

sunny_talwar — Thu, 14 May 2015 11:03:03 GMT

I am not completely sure if I understand your data, but to check if the order by is working or not, why don't you add RowNo() as S.No. to your MemPolicy_Child table and see what order it is sorting everything. This might help you to figure out what you need to do in order to fix the ordering. Else please share a sample.

Best,

Sunny

Re: Sorting by with two levels

Thu, 14 May 2015 11:03:31 GMT

Main Member	Member	Age	Allocated Child Counter	Correct Counter
4006720100	4006720105	16	2	1
4006720100	4006720103	15	1	2

That would be an example of data. I'd like it to first sort by main member and then sort by age, but it seems to be sorting by main member and then member instead.

Re: Sorting by with two levels

sunny_talwar — Thu, 14 May 2015 11:07:35 GMT

You need to sort the age in Descending order.

Try this:

MemPolicy_Child:

LOAD

JOIN_MEM_NO,

If(Main_MEM_NO_TEMP <> Previous(Main_MEM_NO_TEMP), 1,

NumSum(peek(Counter) + 1)) as Counter

RESIDENT MemPolicy_Temp

ORDER BY Main_MEM_NO_TEMP,RENEWAL_AGE desc;

Assuming RENEWAL_AGE is the Age in your below table.

HTH

Best,

Sunny

Re: Sorting by with two levels

Thu, 14 May 2015 12:07:20 GMT

Thank you. Feel like and idiot that it was that small, but thank you.

Re: Sorting by with two levels

sunny_talwar — Thu, 14 May 2015 12:09:19 GMT

No problem. We all make these small errors. But I am glad your were able to fix it.

Best,

Sunny