topic Re: For each Loop, within the SAME table? in QlikView

For each Loop, within the SAME table?

nico_ilog — Mon, 21 Jul 2014 11:07:33 GMT

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

Re: For each Loop, within the SAME table?

avinashelite — Mon, 21 Jul 2014 11:12:41 GMT

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

Re: For each Loop, within the SAME table?

marcus_malinow — Mon, 21 Jul 2014 11:13:40 GMT

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

Re: For each Loop, within the SAME table?

sudeepkm — Mon, 21 Jul 2014 11:22:54 GMT

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.

Re: For each Loop, within the SAME table?

nico_ilog — Mon, 21 Jul 2014 11:25:29 GMT

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?

ashfaq_haseeb — Mon, 21 Jul 2014 11:25:31 GMT

Hi,

look at Peek function.

Regards

ASHFAQ

Re: For each Loop, within the SAME table?

nico_ilog — Mon, 21 Jul 2014 11:25:56 GMT

Thanx Marcus,

Please see my reply to AV.

Re: For each Loop, within the SAME table?

nico_ilog — Mon, 21 Jul 2014 11:29:04 GMT

Sudeep.

Thanx for the comment. Please see my response to AV

Re: For each Loop, within the SAME table?

marcus_malinow — Mon, 21 Jul 2014 11:29:47 GMT

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

Re: For each Loop, within the SAME table?

nico_ilog — Mon, 21 Jul 2014 11:32:21 GMT

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!

Re: For each Loop, within the SAME table?

marcus_malinow — Mon, 21 Jul 2014 11:38:59 GMT

ok,

Trimmed Quote Number is being used for the comparison

Quote Number is just your original Quote Number, in case you need to keep it, and useful for debugging

The Peek condition compares the prior records Trimmed Quote Number with the same value for the current record, and if they're the same it flags the record with a zero (meaning you can ditch that record at a later stage)

Maybe this could be a bit more robust

Instead of

Left([Quote Number], 5)

Use

Left([Quote Number], Index([Quote Number] & '.', '.') - 1)

Re: For each Loop, within the SAME table?

avinashelite — Mon, 21 Jul 2014 11:45:21 GMT

Hope you got the Marcus Malinow explanation, if your still facing any issues please post your app.

Re: For each Loop, within the SAME table?

nico_ilog — Mon, 21 Jul 2014 11:49:34 GMT

Marcus,

The More robust method works better!

But, it doesn't link me to the highest incremented record?

Let me play around with it! It should work! Will revert back if it does/ doesn't!

Rgds,

Nico

Re: For each Loop, within the SAME table?

marcus_malinow — Mon, 21 Jul 2014 11:54:46 GMT

Ok, looks like the ORDER BY clause might not be working correctly.

You could try adding a field like so to determine the ordering that is being applied

RecNo() as RecordNumber

Re: For each Loop, within the SAME table?

nico_ilog — Mon, 21 Jul 2014 13:53:55 GMT

Marcus!

Ok i tried, and im busy with it.. but im stuck! I got as far so that it indicated what record was the first increment! But when i try to select the FINAL incerement, it simply selects all the records, that are NOT the first increment?

What am i missing?

Please see below.

Quotes2:

LOAD

if(WildMatch(QuoteNumber,'*.*') = 1,Right(QuoteNumber, len(QuoteNumber)-Index(QuoteNumber, '.')),'1st Increment') as IncrementedNumber,

QuoteNumber

Resident QuoteHeaders Order By QuoteNumber;

Quotes3:

LOAD

IncrementedNumber as varTestingRec,

QuoteNumber,

if(peek(varTestingRec,-1) < [IncrementedNumber],'Final Increment','Previous Increment') as KeepThisrecord

RESIDENT Quotes2

ORDER BY [QuoteNumber];

Re: Re: For each Loop, within the SAME table?

marcus_malinow — Mon, 21 Jul 2014 13:59:22 GMT

Instead of

Order By QuoteNumber,

Order By QuoteNumber DESC

Re: Re: For each Loop, within the SAME table?

nico_ilog — Mon, 21 Jul 2014 14:04:36 GMT

Absolutely AMAZING!

It works perfectly!!!

Why does that make a Diff?

Re: For each Loop, within the SAME table?

marcus_malinow — Mon, 21 Jul 2014 14:08:41 GMT

Basically, say you have three records:

Q2007

Q2007.2

Q2007.3

You're interested in the one with the largest Quote Number, so ordering in descending order will allow you to pick that up before the other two records.

Re: For each Loop, within the SAME table?

nico_ilog — Mon, 21 Jul 2014 14:14:47 GMT

Ahh! Ok Perfect!

Marcus, your assistance is noted with a High Degree of appreciation!

Re: For each Loop, within the SAME table?

nico_ilog — Tue, 29 Jul 2014 12:08:25 GMT

Good Day Marcus,

Please provide some assistance on the above mentioned.

O can now properly identify the Final Increments.

What is however happening is that fact that the records, which are final increments from the start (i.e. No increments) as situated below.

Po1001

Po1001.1

Po1002 <-- This record is thrown away, because it has no increment.

Po1003

Po1003.1

What can i do to keep those increments? I've been trying some weird combinations, but im not getting it to work?

Please see code below:

//PO

POReworkTableLoad:

LOAD

if(WildMatch(IncrementedPONumber,'*.*') = 1,Right(IncrementedPONumber, len(IncrementedPONumber)-Index(IncrementedPONumber, '.'))) as FinalPoNumber,

IncrementedPONumber as Increments,

Left(order_number,6) as LeftOrderNUm,

order_number ;

LOAD

if(NOT WildMatch(order_number,'*.*'),order_number&'.0',order_number) as IncrementedPONumber,

order_number

Resident POs Order By order_number DESC;

POReworkTableFinal:

LOAD

FinalPoNumber as varTestingPORec,

order_number,

if(peek(varTestingPORec,-1) < [FinalPoNumber],'Final Increment','Previous Increment') as KeepThisPOrecord

RESIDENT POReworkTableLoad

ORDER BY [order_number] DESC;