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

28 Replies
marcus_malinow
Partner - Specialist III
Partner - Specialist III

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)

avinashelite

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

nico_ilog
Partner - Creator II
Partner - Creator II
Author

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

marcus_malinow
Partner - Specialist III
Partner - Specialist III

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

nico_ilog
Partner - Creator II
Partner - Creator II
Author

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];

After selection.JPG.jpgBefore Selection.JPG.jpg

marcus_malinow
Partner - Specialist III
Partner - Specialist III

Instead of

Order By QuoteNumber,

Order By QuoteNumber DESC

nico_ilog
Partner - Creator II
Partner - Creator II
Author

Absolutely AMAZING!

It works perfectly!!!

Why does that make a Diff?

marcus_malinow
Partner - Specialist III
Partner - Specialist III

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.

nico_ilog
Partner - Creator II
Partner - Creator II
Author

Ahh! Ok Perfect!

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

nico_ilog
Partner - Creator II
Partner - Creator II
Author

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;