topic Create New Field in Script in QlikView

Create New Field in Script

mazacini — Wed, 16 Feb 2011 00:36:59 GMT

I want to creat a new field in my table that ranks the instance of TR.REF for each SORDREF.

So, instance of TR.REF 282117 for SORDREF 142871 is 1, instance of 283479 is 2, etc as per below

DATE	SORDREF	TR.REF	ITEMCODE	NEW FIELD
04/01/2010	142871	282117	33E01 -(222901)	1
04/01/2010	142871	282117	33E10 -(222902)	1
04/01/2010	142871	282117	MTCRCA09	1
04/01/2010	142871	282117	ZR5RT011	1
04/01/2010	142871	282117	1722.47	1
04/01/2010	142871	283479	333E - (222964)	2
04/01/2010	142871	283981	1707	3
04/01/2010	142871	285213	ZR5MF011	4
04/01/2010	142872	282070	10060653	1
04/01/2010	142872	282070	10066401	1
04/01/2010	142873	282071	10060653	1
04/01/2010	142874	282049	013 - 4	1
04/01/2010	142874	282049	4861.063	1
04/01/2010	142874	289943	2202.0315	2
04/01/2010	142874	289944	2202.0315	3
04/01/2010	142875	282036	690 DZII	1
04/01/2010	142876	282057	BB2718	1
04/01/2010	142876	282057	E2718O	1
04/01/2010	142877	282079	OBS/4863.063	1

PS - am a Personal Edition user

Create New Field in Script

pover — Wed, 16 Feb 2011 01:24:52 GMT

After ordering the table properly use the previous() function to evaluate the previous row values. For example, you can try:

Load DATE, SORDREF,TR.REF,ITEMCODE,
if(SORDREF<>previous(SORDREF), 1,
if(TR.REF=TR.REF, previous(NEW_FIELD),
if(TR.REF=TR.REF, previous(NEW_FIELD) + 1))) as NEW_FIELD
From Table;

Regards.

Create New Field in Script

mazacini — Wed, 16 Feb 2011 02:07:14 GMT

Hi Karl

1.How do I ensure the table is ordered properly?

2. I am getting a Field Not Found - <NEW_FIELD> error message when I paste in your script. Am I missing something?

Joe

Create New Field in Script

johnw — Wed, 16 Feb 2011 02:11:03 GMT

1) User ORDER BY. Perhaps ORDER BY TR.REF, but I'm not clear on the exact requirement.
2) Use peek(NEW_FIELD) instead of previous(NEW_FIELD). I make this mistake all the time. Keep using previous(SORDREF) though. Peek refers to fields in the table you're creating. Previous refers to fields in the table you're reading in. SORDREF exists in the table you're reading in, but NEW_FIELD does not, so that's why you got the error.

Create New Field in Script

pover — Wed, 16 Feb 2011 02:20:10 GMT

John is right. I just saw a couple other errors in my script. The second and third condition should refer to the prevoius TR.REF and the third condition should be not equal, <>. Sorry.

Load DATE, SORDREF,TR.REF,ITEMCODE,
if(SORDREF<>previous(SORDREF), 1,
if(TR.REF=previous(TR.REF), peek(NEW_FIELD),
if(TR.REF<>previous(TR.REF), peek(NEW_FIELD) + 1))) as NEW_FIELD
From Table;

Create New Field in Script

mazacini — Wed, 16 Feb 2011 02:39:14 GMT

1) I know this is basic stuff, but I can't get the Order By to work - am getting a "Garbage after Statement" - I placed it at the end of my LOAD script (load from csv), after the FROM and WHERE clauses.

2) I am only getting a Value 1 in the NEW_FIELD. It may be something to do with the Sort Order? (I nottice that the suggested script repeats condition TR.REF=TR.REF in the IF statement. I'm not sure if that is significant, and at nearly 2am, mind is a bit fuzzy)

Create New Field in Script

mazacini — Wed, 16 Feb 2011 02:49:47 GMT

My last message crossed with Karls.

I changed my script as advised.

I am still only getting value 1 in NEW_FIELD. Maybe it is the SORT problem?

Create New Field in Script

mazacini — Wed, 16 Feb 2011 02:56:07 GMT

Thanks for your help so far. I have to sign off for now. If you have any more suggestions, I can look at them in the morning

Create New Field in Script

pover — Wed, 16 Feb 2011 05:40:07 GMT

If you get a get the garbage after statement it is because you can't do a direct order by when loading in a CSV. I don't know why, but the way to get around that is to load the csv first and then call it again in the script through the Resident command that does support the use of the order by.

CSV_Table:
Load *
From file.csv;

Final_Table:
Load *, New_Field
Resident CSV_Table
Order by DATE,SORDREF,TR.REF,ITEMCODE;

Regards.

Create New Field in Script

mazacini — Wed, 16 Feb 2011 12:37:35 GMT

Hi Karl

Getting the error below, which I think is down to my lack of knowledge about basic scripting generally and use of RESIDENT specifically.

NEW_FIELD is now DEL_COUNT. Trans1 is the name given to the table during load, being the name of the source csv file. I don't normally precede the LOAD with the table name, but have done so in this case ie I inserted a line Trans1: before the LOAD script.

I added only the "Final_Table" part of the script above.

The Final_Table seems to have replaced / renamed a different table ("ITEMS1")., which is a table loaded directly after the "Final_Table" script above.

The good news is that I can sort the source csv by SORDREF, and (if I leave out the RESIDENT script) get the NEW_FIELD to calculate properly, so the resolution of this error is not critical.

I'm sure I have taken up enough of your time already on this. But if you could point me in the right direction on my error, i would appreciate it.

Joe

Create New Field in Script

pover — Wed, 16 Feb 2011 18:37:02 GMT

I'm not sure why you get an error by just looking at the error message. Can you post your script to investigate the source of the error?

Regards.

Create New Field in Script

mazacini — Wed, 16 Feb 2011 21:37:26 GMT

Hi Karl

Script attached.

Joe

Create New Field in Script

mazacini — Wed, 16 Feb 2011 21:41:45 GMT

Hi Karl

In fact i had commented out the Final Table section. In the meantime, I noticed that I had inserted a new LOAD statement from Custs1.csv.

In fact the script is as per attached.

Joe

Create New Field in Script

johnw — Wed, 16 Feb 2011 22:59:09 GMT

You named the table TRANS1 then tried to refer to it as Trans1. I believe QlikView is case-sensitive.

Create New Field in Script

mazacini — Wed, 16 Feb 2011 23:08:17 GMT

Thanks John.

New Error "Field Names must be unique within table".Can you take a look?

I may be overstaying my welcome on this post, but hopefully this will be the last issue.

Joe

Create New Field in Script

johnw — Wed, 16 Feb 2011 23:38:36 GMT

You're doing this:

Load *, DEL_COUNT

So you're telling it to load ALL fields, and then repeating DEL_COUNT. You can't load DEL_COUNT twice in the same table. If you were trying to add a field to avoid the automatic concatenation, that's not the way to do it. Instead, use noconcatenate.

Final_Table:
NOCONCATENATE
LOAD *
RESIDENT TRANS1
...

Create New Field in Script

mazacini — Thu, 17 Feb 2011 00:20:28 GMT

Hi John / Karl

The script now runs error free. I have to say I'm not sure whats going on with that part of the script. It seems to have created additional syn keys, which I also don't understand.

All things I can investigate myself in time.

Time to move on.

Thanks for all your help.

Joe

Create New Field in Script

pover — Thu, 17 Feb 2011 00:26:16 GMT

You are probably needing a

Drop table TRANS1;

at the end of the script.