topic Re: selecting which records import during scripting in QlikView

selecting which records import during scripting

Tue, 21 Feb 2012 23:31:08 GMT

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

selecting which records import during scripting

CELAMBARASAN — Wed, 22 Feb 2012 05:15:11 GMT

Hi,

Check with this,

MainTable:

Load

ID,

LoadLine,

Origin,

Destination

From DataFile;

Result:

Load

ID,

Max(LoadLine) as MaxLoadLine,

MinString(Origin) as OriginMin,

MaxString(Destination) as DestMax

Resident

MainTable

Group by ID;

Hope it helps

Celambarasan

selecting which records import during scripting

Thu, 23 Feb 2012 18:49:21 GMT

thanks Celambarasan. It works on some records but not all. Here is my actual script

result:

load

`wo_id`,
max(load_no) as MaxLoad_no,
minstring(shipcity) as ShipcityMin,
MaxString(conscity) as ConscityMax

resident

MainTable
group by `wo_id`;

it seems to be incomplete at the below line.

MaxString(conscity) as ConscityMax

It does not always grab the max consignee but still grabs the conscity value from load_no 1 not the max. When I comment out

max(load_no) as MaxLoad_no

nothing changes so I'm wondering if I was supposed to do something else with this value.

Re: selecting which records import during scripting

swuehl — Thu, 23 Feb 2012 19:04:07 GMT

If you use something like maxstring(conscity), this will indeed look for the maxstring of field conscity in the record set grouped by 'wo_id', but regardless of the load_no. In your above sample data, the sort order of the strings are aligned with the order of load_no, I assume that is not always the case, right?

Then you need to use FirstSortedValue to retrieve the consciety for the max or min load_no:

load

`wo_id`,
FirstSortedValue(shipcity,load_no) as ShipcityMin,
FirstSortedValue(conscity, -load_no) as ConscityMax

resident

MainTable
group by `wo_id`;

The line calculating the MaxLoad_no can indeed be removed, if you don't need it.

Hope this helps,

Stefan

selecting which records import during scripting

Thu, 23 Feb 2012 20:12:01 GMT

that did the trick!

fantastic. Thank you

selecting which records import during scripting

Sun, 26 Feb 2012 16:18:10 GMT

Stefan

just a follow up question. I added the below to my script in order to bring in more fields I need and it works perfect thanks to your help.

FirstSortedValu(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

but, I created a chart with a simple straight table. The value for driverIDMax in the straight table chart needs to actually be the driver's name rather than the ID number. the ID number is all that is available in this table. I have the table in my script that imports all the drivers and their IDs, but how do I get the expression to compare the driverIDMax value in my record to the table of drivers and return the driver's name?

any idea?

thanks

Lucas

Re: selecting which records import during scripting

swuehl — Sun, 26 Feb 2012 23:07:58 GMT

Are your driver's names unique or could it be that the same name belongs to multiple driver IDs, like common name like Smith? I assume latter.

How are your tables linked to each other? And what is the dimension of that straight table?

You could try something like

=aggr( if( driverIDMax = driverID, driverName), driverID)

as expression in a table with e.g. dimension driverIDMax, where driverID and driverName are fields of your driver table.

edit:

or just add the driver's name to your table containing the driverIDMax in your load, an additional FirstSortedValue() should do.

selecting which records import during scripting

Mon, 27 Feb 2012 02:20:33 GMT

The dimesion of my straight table is customer. The tables are all linked to the main table by several different fields. I have about 10 tables. The driver table is linked to the main table by DriverID. All the the drivers have unique driverNames with a system assigned unique DriverID when they are created. The driverID is the only field available in the main table. I just need to link the driverIDMax to the driver table and return the corresponsing driverName.

I'm basically trying to say look up driverIDMax in the driver table and return driverName becasue driver name is not a field in my main table. Only driverID is available.

Re: selecting which records import during scripting

swuehl — Mon, 27 Feb 2012 11:13:43 GMT

If you want to use somekind of lookup, you could use the lookup() function to lookup the driver name in the driver's table and create a new field in some other table from that.

If you don't want to create a new field in the script, but want to lookup the value in the frontend e.g. in a straight table, you could substitute the lookup function with a fieldvalue/fieldindex combination (the lookup function does not exist outside the script), using this as expression:

=fieldvalue('driverName',fieldindex('driverid',driverIDMax))

Here you want to retrieve the driver's name from field driverName, and you calculate the index by comparing the driverid to driverIDMax. You need to replace driverName, driverid and driverIDMax with your correctly spelled field names.

This should work assuming that

- your driverName and driverid fields only have unique values (you already confirmed this), and have a 1:1 relation based on the load order, this is the case if they are located in one table like

driverid driverName

1 Steve

2 Peter

- in your straight table with dimension customer, each line of the table results in one unambiguous value of driverIDMax

You could also try the expression I posted in my previous post:

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

Hope this helps,

Stefan

selecting which records import during scripting

Mon, 27 Feb 2012 15:42:07 GMT

ok getting closer. Both suggetsions above yielded a DriverName. but, Fieldvalue....returned the wrong drivername. although the driver table is setup as you demonstrated

driverID DriverName

1 Steve

2 Peter

The actual driverID is an alphanumeric code. Not sure if it matters.

the aggr...expression worked perfect...except it doesn't not work on every subsequent record. Only the first record in the straight table that has the driverID. So for example, when I select specific records (say 10 recrods) and DriverID 0000b is on the 3rd and 5th uniqe record, only the third record will show the driverName with the 5th record showing nothing.

perhaps the best couse of action is work it in the script as you suggested. Would that be the best way and what would I use?

selecting which records import during scripting

swuehl — Mon, 27 Feb 2012 15:55:07 GMT

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,

...

selecting which records import during scripting

Mon, 27 Feb 2012 17:30:30 GMT

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.

selecting which records import during scripting

swuehl — Mon, 27 Feb 2012 19:17:14 GMT

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

selecting which records import during scripting

Mon, 27 Feb 2012 19:36:50 GMT

WE GOT IT!! Thanks Stefan!