topic Re: how to create an unique key based on previous values in QlikView

how to create an unique key based on previous values

Wed, 10 Oct 2012 14:54:17 GMT

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!

Re: how to create an unique key based on previous values

Thu, 11 Oct 2012 09:43:47 GMT

I usually hate to bump, but is the question not so clear, or is the problem untackleable?

Thank you!

Re: how to create an unique key based on previous values

Thu, 11 Oct 2012 14:29:42 GMT

I've got somewhere 1 inch further and wrote down this piece of script

LOAD ItemA, 
     ItemB
FROM
[\IC_test.xlsx]
(ooxml, embedded labels, table is Sheet1);
let vMaxRow = NoOfRows('Sheet1'); //get the tot number of rows in source table
For vValueNo=1 to $(vMaxRow) //loop through every row
NewTable:
LOAD
ItemA,
ItemB,
if(peek('ItemA',$(vValueNo),'Sheet1') <> peek('ItemA',($(vValueNo)-1),'Sheet1'), 'Key'&$(vValueNo), 'SAME') as Chain
RESIDENT Sheet1;
NEXT vValueNo
Drop table Sheet1;

expectations: have the script looping around my data (just one field, ItemA, to make it simpler) and check if the previous value in ItemA is the same, input SAME in the Chain field, otherwise create a value as Key&NumberOfTheRecord

result: the script picks up the whole table Sheet1 and attaches a 'Key1' value in the Chain field; then it picks up again the whole table, and attaches a 'Key2' and so forth.

Given my complete lack of experience in programming, I'm quite happy about my result, at least I managed to get a loop started; but still I'm a long way to reach what I want (have the script check current value against ALL the previous values, and if happens to be a new value, assign a new key, else assign the same key as the old value)

Any hint will be much, much appreciated!

Re: how to create an unique key based on previous values

chris_johnson — Thu, 11 Oct 2012 15:33:06 GMT

Hi,

I think you're close but when you are creating NewTable, instead of doing Resident Sheet1 do AutoGenerate(1). This will only create one line.

You will need to change a few things before this too so it'll look something like this:

let vPrevItemA = null();

let vPrevItemB = null();

let vChain = null();

For vValueNo=0 to $(vMaxRow)-1 //Changed this to help with peeks (0 indexed)

let vItemA = peek('ItemA',$(vValueNo),'Sheet1');

let vItemB = peek('ItemB',$(vValueNo),'Sheet1');

if '$(vItemA)'<>'$(vPrevItemA)' then

let vChain = 'Key'&$(vValueNo);

endif;

NewTable: LOAD '$(vItemA)' as ItemA, '$(vItemB)' as ItemB, '$(vChain)' as Chain autogenerate(1);

let vPrevItemA = '$(vItemA)';

let vPrevItemB = '$(vItemB)';

NEXT vValueNo;

It's off the top of my head so may need a bit of tinkering with to get it to work!

Hope it helps,

Chris

Re: how to create an unique key based on previous values

Thu, 11 Oct 2012 15:52:43 GMT

Dear Chris,

your help was of great value!

I really made a gigantic leap and it is starting to get closer to what I want.

The first small issue that I have is that if my source table looks like this

ItemA	ItemB	Key
AAA	BBB	Key0
AAA	CCC	Key0
EEE	FFF	Key1
AAA	HHH	Key0
LLL	MMM	Key2

it is important that line 4, since it contains AAA which was already in lines 1 and 2, still keeps the same key as the first ones, hence Key0.

And then, on a second phase, I will need my check for a new line to be done both on ItemA AND ItemB, by looking back both at ItemA AND ItemB.

But, as said before, this oen of yours was already an outstanding hint in the right direction, thank you so much!

I definitely have some material to work upon tomorrow

Re: how to create an unique key based on previous values

chris_johnson — Thu, 11 Oct 2012 16:08:20 GMT

Ah, ok!

For your first problem maybe you need to sort your data first on ItemA,ItemB before going into the loop.

I'm not sure if you can sort the data when you are loading from Excel though so you may need to load it into QlikView first and then sort it by loading the data again. Check if you can sort it on the load from Excel first though.

I will leave you to have a go at your second problem but you should have most of what you need

Regards,

Chris

Re: how to create an unique key based on previous values

Fri, 12 Oct 2012 08:47:34 GMT

Sort is not going to work, as if I take the examble above

ItemA	ItemB	Key
AAA	BBB	Key0
AAA	CCC	Key0
EEE	FFF	Key1
AAA	HHH	Key0
LLL	MMM	Key2

and then let say I add the line

MMM

BBB

Key0

as you see the Key has to be Key0, because BBB needs to be chainded to the (AAA) BBB.

But because of sort, this new line will stay after the last line of the existing table, therefore generating a new Key.

So I think is rather complicate, I have to find a way for each new line to look back at ALL previous lines and check against the new values.

Thank you again

Re: how to create an unique key based on previous values

chris_johnson — Fri, 12 Oct 2012 09:24:45 GMT

Hi,

Had a bit of a play around and feel like I'm getting closer. Have changed some of the functions I'm using, have a look and see what you think!

Regards,

Chris

Re: how to create an unique key based on previous values

Fri, 12 Oct 2012 09:37:36 GMT

Dear Chris,

this is really another greatly helpful step.

Now I just need to adjust the if statements for having the script cross-looking both at ItemA and ItemB, and I will get what I need!

but I will try on my own, now the nasty variables/loop part is sorted, I think I can handle myself with setting up rightly the IF statements...

I will come back here, really thank you very much again for your valuable help!!

Re: how to create an unique key based on previous values

chris_johnson — Fri, 12 Oct 2012 09:40:45 GMT

Glad I could help.

I'm sure you'll be whizzing through this in no time, it just takes a little practise and knowledge of a few key functions that you may not have used before.

Chris

Re: how to create an unique key based on previous values

Fri, 12 Oct 2012 11:34:28 GMT

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!

Re: how to create an unique key based on previous values

chris_johnson — Fri, 12 Oct 2012 12:42:27 GMT

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.

Re: how to create an unique key based on previous values

Fri, 12 Oct 2012 12:49:54 GMT

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!

Re: how to create an unique key based on previous values

chris_johnson — Fri, 12 Oct 2012 13:11:36 GMT

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

Re: how to create an unique key based on previous values

Fri, 12 Oct 2012 13:16:59 GMT

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!

Re: how to create an unique key based on previous values

chris_johnson — Fri, 12 Oct 2012 13:22:43 GMT

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

Re: how to create an unique key based on previous values

Fri, 12 Oct 2012 13:38:44 GMT

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!

Re: how to create an unique key based on previous values

chris_johnson — Fri, 12 Oct 2012 13:44:29 GMT

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

Re: how to create an unique key based on previous values

Fri, 12 Oct 2012 13:45:56 GMT

script-wise it defintiely looks new to me.

will check and let you know asap!

thanks!