selecting which records import during scripting

[#1]

for every unique identifier in my table, I have two or three records. For example my table looks like this

ID load line Origin Destination

ABC 01 X B

ABC 02 Y C

ABD 01 XX D

ABD 02 YY E

ABF 01 XXX F

ABF 02 YYY G

ABF 03 ZZZ H

For the first record, I need my script to look first at the ID and recognize that they are related (don’t know if that matters), then bring in origin matching load line 01, but destination matching load line 2. ie origin needs to be x, destination C.

For ID ABF, it would need to look at the highest value of load line (in this case 03) and bring back that destination. For this example I used 3, but it could be 04 or 05 or whatever.

Any help you could give would be great. I hope I was clear, if not please let me know as I am a very new member to Qlikview.

Thanks

[swuehl]

The problem with aggr() and not showing the name on all lines is probabling due to the implicit DISTINCT qualifier of the aggr() function, try a NODISTINCT:

=aggr(NODISTINCT if( driverIDMax = driverid, driverName), driverid)

Not sure why the fieldvalue(fieldindex()) combination does shows the wrong name, the ids and names are unique.

But using fieldvalue and fieldindex are kind of problematic since they are operating on the compressed fields. One idea, is one of the two fields already used before loading the driver's table, maybe driverid? This will determine the load order of that field. Well, anyway, seems to be too complicated for now.

If you want to use the lookup function, the syntax is described in the Help like:

lookup(fieldname, matchfieldname, matchfieldvalue [, tablename])

Returns the value of fieldname corresponding to the first occurrence of the value matchfieldvalue in the field matchfieldname.

Fieldname, matchfieldname and tablename must be given as strings (e.g. quoted literals).

The search order is load order unless the table is the result of complex operations such as joins, in which case the order is not well defined.

Both fieldname and matchfieldname must be fields in the same table, specified by tablename. If tablename is omitted the current table is assumed.

If no match is found, null is returned.

Example:

lookup('Price', 'ProductID', InvoicedProd, 'pricelist')

So your lookup in the table containing driverIDMax could look similar to:

...

lookup('driverName','driverid', driverIDMax, 'driverTable') as driverIDMaxName,

...

[#3]

the lookup

lookup('driverName','driverid', driverIDMax, 'driverTable') as driverIDMaxName

crashes because it can't find the field DriverIDMax

I'm guessing because it's not in the 'driver table' as it comes from

FirstSortedValue(driverid,-load_no) as driverIDMax

so I tried

Lookup('drivername','driverid',FirstSortedValue(driverid,-load_no,'carrier')) as driverIDMaxName,

it didn't work.  Thanks for all your help and sorry to keep bother you.  I tried the community posts and couldn't find the answer.

[swuehl]

Just for interest:

Has the issue with the aggr() not showing up in all lines has been resolved using NODISTINCT qualifier?

Now back to your lookup() function in the script:

I assume you want to add a new field with the name to the table that also contains the driverIDMax, driverIDMin (essentially the table that you created above using the FirstSortedValue() functions).

in your above lookup function, I think you positioned one bracket at the wrong place, it should look like:

Lookup('drivername','driverid',FirstSortedValue(driverid,-load_no),'carrier') as driverIDMaxName,

or use e.g. a preceding load like:

LOAD *,

lookup('drivername','driverid', driverIDMax, 'carrier') as driverIDMaxName;

Load

`wo_id`,

FirstSortedValue(driverid,load_no) as driverIDMin,
FirstSortedValue(driverid,-load_no) as driverIDMax,

FirstSortedValue(shipname,load_no) as ShipNameMin,
FirstSortedValue(shipcity,load_no) as ShipcityMin,
FirstSortedValue(shipprov,load_no) as ShipProvMin,
FirstSortedValue(shippostal,load_no) as ShipPostalMin,

FirstSortedValue(`cons_name`,-load_no) as ConsigNameMax,
FirstSortedValue(conscity,-load_no) as ConsigcityMax,
FirstSortedValue(consprov,-load_no) as ConsigProvMax,
FirstSortedValue(conspostal,-load_no) as ConsigPostalMax

resident

MainTable
group by `wo_id`;

and always check the correct spelling of all field names, field names are case sensitive.

Regards,

Stefan

[#5]

WE GOT IT!!  Thanks Stefan!