how to create an unique key based on previous values

[#1]

Dear guys

I have a sort of a puzzle (for me); for you I think is something really easy!

let say I have a list of items

ItemA	ItemB	Chain
A	B	Key0001
D	E	Key0002
D	F	Key0002
G	H	Key0003
L	N	Key0004
M	N	Key0004
P	A	Key0001

I think it speaks for itself, but I will try in my own words.

I'm trying to create, out of a table with two fields (ItemA and ItemB), a new field "Chain" which assigns a Key.

This Key is generated according to what is the content in ItemA and ItemB.

If the two fields contain brand new values, then a new Key is created in the Chain field.

Otherwise, if it in ItemA/B there is an item which is previously in the list, it should assign the Key which was assigned previously.

Is it a hard task?

Thank you!

[#2]

Well, it took me a little more than no time, but I'm getting even closer.

Please see attached, if you are still willing to lend a hand.

But indeed I am facing a structural problem, as, if this is the table

ItemA	ItemB	Chain
AAA	BBB	Key0
AAA	HHH	Key0
AAA	QQQ	Key0
HHH	ZZZ	Key0
LLL	MMM	Key1
MMM	BBB	Key1...but Key0 too!!
NNN	OOO	Key2
QQQ	TTT	Key1
RRR	AAA	Key0

The entry MMM - BBB creates a problem, as it is part of two "chains" and the script cannot go back to the previously entered record, to which a new chain key was assigned, and change the Key from Key1 to Key0.

But besides this structural problem (this situation I'm not expecting to happen in real life) some fields still stays empty.

Again, if you fancy giving it a last revision, it will be much appreciated!

thank you!

[chris_johnson]

Just to clear it up, what should be the key for the row MMM - BBB? Key 0 or key 1?

I think maybe the order of the if statements may need to be re-arranged in order to get the result you want.

I think I've sorted out the blank lines anyway! I took out a noconcatenate keyword which was not needed and that seemed to do the trick.

[#4]

yep, the tricky part is that not only MMM - BBB should be Key0.

But the previous record, LLL-MMM, since it has the MMM key which NOW belongs to Key0, should also be changed to Key0.

But this is really hard to achieve and I guess such situation will never arise in real life.

I will give a look to your fix, will let you know.

Though it seems I also reached something with a different code, which indeed does not seem logically written to me..

I attached it...

From a logic point of view, I really do not explain to myself how is it working...

thank you very much again!

[chris_johnson]

Yes, there are many ways to do the same thing in QlikView!

What I have done is changed the order of the if statements so that it works i the following way for the current row in the table:

1. If the value in ItemA matches a previous ItemA then use the key for that row

2. If the value in ItemB matches a previous ItemB then use the key for that row

3. If the value in ItemA matches a previous ItemB then use the key for that row

4. If the value in ItemB matches a previous ItemA then use the key for that row

5. If ItemA and ItemB values are unique then create a new key

As for the problem of then assigning row LLL-MMM to Key0 then this gets trickier. I think it may need to then be re-done somehow, but I'm not sure how!

I hope i have been of some help anyway, and hopefully you have learnt a few tricks

Chris

[#6]

Chris Abwat-Johnson wrote:

Yes, there are many ways to do the same thing in QlikView!

that is the positive and negative side of it

Your conditions, as you wrote them down, sounds good.

and easily tweakable if I will need to change it (to eventually fits at best the "tricky scenario")

but the file you attached seems the old version to me, I think is the same one as the first you posted today...

Can you please double check?

Thank you again for all of your effort!

[chris_johnson]

Oh, I'm not sure. What is the value for the Chain column for row MMM-BBB? In the current version I have it is Key0.

I have attached it again, but it may be the same as before! Maybe I didn't save the file before I attached it.

Chris

[#8]

MMM-BBB is indeed Key0 as it is supposed to be, but the cross-check within the two fields is not working and the script is the same (I know it almost by heart now ) as that awesome piece of script that you post this morning, that fixed the issue of looking back in the whole range (of the same field) instead of just the previous value..

So I'm afraid you didn't hit the save button!

pity, but maybe I can sort something out with that "odd" version of mine that I attached..

thanks!

[chris_johnson]

Yes, sorry. My fault! Try this one, although I'm a bit lost with them all now! 

[#10]

script-wise it defintiely looks new to me.

will check and let you know asap!

thanks!