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]

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

Thank you!

[#3]

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!

[chris_johnson]

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

[#5]

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

[chris_johnson]

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

[#7]

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

[chris_johnson]

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

[#9]

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!!

[chris_johnson]

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