Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

mazacini
Contributor III

Create New Field in Script

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

DATESORDREFTR.REFITEMCODENEW FIELD
04/01/201014287128211733E01 -(222901)1
04/01/201014287128211733E10 -(222902)1
04/01/2010142871282117MTCRCA091
04/01/2010142871282117ZR5RT0111
04/01/20101428712821171722.471
04/01/2010142871283479333E - (222964)2
04/01/201014287128398117073
04/01/2010142871285213ZR5MF0114
04/01/2010142872282070100606531
04/01/2010142872282070100664011
04/01/2010142873282071100606531
04/01/2010142874282049013 - 41
04/01/20101428742820494861.0631
04/01/20101428742899432202.03152
04/01/20101428742899442202.03153
04/01/2010142875282036690 DZII1
04/01/2010142876282057BB27181
04/01/2010142876282057E2718O1
04/01/2010142877282079OBS/4863.0631


PS - am a Personal Edition user

1 Solution

Accepted Solutions
Luminary
Luminary

Create New Field in Script

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.

17 Replies
Luminary
Luminary

Create New Field in Script

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.

mazacini
Contributor III

Create New Field in Script

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

MVP
MVP

Create New Field in Script

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.

Luminary
Luminary

Create New Field in Script

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;

mazacini
Contributor III

Create New Field in Script

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)

mazacini
Contributor III

Create New Field in Script

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?

mazacini
Contributor III

Create New Field in Script

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

Luminary
Luminary

Create New Field in Script

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.

mazacini
Contributor III

Create New Field in Script

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