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
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?
I've got somewhere 1 inch further and wrote down this piece of script
(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
if(peek('ItemA',$(vValueNo),'Sheet1') <> peek('ItemA',($(vValueNo)-1),'Sheet1'), 'Key'&$(vValueNo), 'SAME') as Chain
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!
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);
'$(vItemA)' as ItemA,
'$(vItemB)' as ItemB,
'$(vChain)' as Chain
let vPrevItemA = '$(vItemA)';
let vPrevItemB = '$(vItemB)';
It's off the top of my head so may need a bit of tinkering with to get it to work!
Hope it helps,
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
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
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
Sort is not going to work, as if I take the examble above
and then let say I add the line
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
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!!