Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

how to create an unique key based on previous values

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
ABKey0001
DEKey0002
DFKey0002
GHKey0003
LNKey0004
MNKey0004
PAKey0001

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!

18 Replies
Not applicable
Author

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

Thank you!

Not applicable
Author

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
Creator III
Creator III

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

Not applicable
Author

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

ItemAItemBKey
AAABBBKey0
AAACCCKey0
EEEFFFKey1
AAAHHHKey0
LLLMMMKey2

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
Creator III
Creator III

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

Not applicable
Author

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

ItemAItemBKey
AAABBBKey0
AAACCCKey0
EEEFFFKey1
AAAHHHKey0
LLLMMMKey2

and then let say I add the line

MMMBBBKey0

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
Creator III
Creator III

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

Not applicable
Author

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
Creator III
Creator III

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