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

[CELAMBARASAN]

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

[#3]

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.

[swuehl · Accepted solution]

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

[#5]

that did the trick!

fantastic.  Thank you

[#6]

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

[swuehl]

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.

[#8]

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.

[swuehl]

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

[#10]

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?