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: 
nico_ilog
Partner - Creator II
Partner - Creator II

For each Loop, within the SAME table?

Good Day!!

I'm hoping someone can help me in implementing the following logic inside the Script.

Load

Record1 then

Load Record2 then

Compare Record1 vs Record2 then

## Do some logic

Repeat

How can i do something like that?

Whoever's assistance is much appreciated!

Nico

1 Solution

Accepted Solutions
marcus_malinow
Partner - Specialist III
Partner - Specialist III

Hi Nico,

try this for your final load

POReworkTableFinal:

NOCONCATENATE LOAD

LeftOrderNUm,

FinalPoNumber as varTestingPORec,

order_number,

if(peek('LeftOrderNUm',-1) = [LeftOrderNUm],'Previous Increment', 'Final Increment') as KeepThisPOrecord

RESIDENT POReworkTableLoad

ORDER BY order_number desc;

View solution in original post

28 Replies
avinashelite

Hi Nico,

Use IF Then else condition looping.

The if..then control statement is a script selection construct forcing the script execution to follow different paths depending on one or several logical conditions. The syntax is:

if condition then

  [ statements ]

{ elseif condition then

  [ statements ] }

[ else

  [ statements ] ]

end if

Where:

condition is a logical expression which can be evaluated as true or false.

statements is any group of one or more QlikView script statements.

Since the if..then statement is a control statement and as such is ended with either a semicolon or end-of-line, each of its four possible clauses (if..then, elseif..then, else and end if) must not cross a line boundary.

Examples:

if a=1 then

load * from abc.csv;

sql select e, f, g from tab1;

end if

if a=1 then; drop table xyz; end if;

if x>0 then

load * from pos.csv;

elseif x<0 then

load * from neg.csv;

else

load * from zero.txt;

end if

marcus_malinow
Partner - Specialist III
Partner - Specialist III

Hi Nico,

I'd suggest using the peek function. As long as the  ordering of your table is correct you should be able to avoid loops and still compare records.

You'll need to do a RESIDENT load from your previously loaded table, and make sure you have an ORDER BY clause.

Marcus

sudeepkm
Specialist III
Specialist III

can you please explain a little bit of your requirement.

if you just want to load the data from the second table based on the field values of table 1 then in that case the exist function can be useful.

if you want to do a record by record compare then for loop with peek function to fetch row wise data is required.

nico_ilog
Partner - Creator II
Partner - Creator II
Author

Avinash,

Many thanx for the reply! But i dont want to load from different table, based on the outcome of a condition.

The scenario is this:

Quotations are created, then as they are ammended, they start superseeding the previous increments. I need to use only the last increment, and not the previous increments.

See below:

Quote number

Q1002 - FIRST INCREMENT

Q1003

Q1002.1

Q1002.2

Q1002.3

Q1002.4 - LAST AMMENDMENT <-- THIS IS THE RECORD I WANT TO LOAD.

Q1003.1

So my logic is, load the first record, Left function by the ".", then load the second record. If they are the same, store the bigger increment as the record name that has been "Lefted".

Q1002 will be stored, using the values of Q1002.4

I hope you can follow the logic im trying to portray?

ashfaq_haseeb
Champion III
Champion III

Hi,

look at Peek function.

Regards

ASHFAQ

nico_ilog
Partner - Creator II
Partner - Creator II
Author

Thanx Marcus,

Please see my reply to AV.

nico_ilog
Partner - Creator II
Partner - Creator II
Author

Sudeep.

Thanx for the comment. Please see my response to AV

marcus_malinow
Partner - Specialist III
Partner - Specialist III

Ok then,

something like this

LOAD Left([Quote number], 5) as TrimmedQuoteNbr

[Quote Number]

if(peek('TrimmedQuoteNbr', -1) =  left([Quote Number], 5),0, 1) as KeepThisrecord

RESIDENT SourceTable

ORDER BY [Quote Number] desc

nico_ilog
Partner - Creator II
Partner - Creator II
Author

Yea!! Something like that! Just briefly, explain what this line does for the script?

LOAD Left([Quote number], 5) as TrimmedQuoteNbr

[Quote Number] <-- This line?

if(peek('TrimmedQuoteNbr', -1) =  left([Quote Number], 5),0, 1) as KeepThisrecord

RESIDENT SourceTable

ORDER BY [Quote Number] desc

I'll give it a shot shortly!