Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
nico_ilog
Contributor 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
Valued Contributor III

Re: For each Loop, within the SAME table?

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;

28 Replies

Re: For each Loop, within the SAME table?

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
Valued Contributor III

Re: For each Loop, within the SAME table?

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
Valued Contributor III

Re: For each Loop, within the SAME table?

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
Contributor II

Re: For each Loop, within the SAME table?

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?

Re: For each Loop, within the SAME table?

Hi,

look at Peek function.

Regards

ASHFAQ

nico_ilog
Contributor II

Re: For each Loop, within the SAME table?

Thanx Marcus,

Please see my reply to AV.

nico_ilog
Contributor II

Re: For each Loop, within the SAME table?

Sudeep.

Thanx for the comment. Please see my response to AV

marcus_malinow
Valued Contributor III

Re: For each Loop, within the SAME table?

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
Contributor II

Re: For each Loop, within the SAME table?

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!

Community Browser