Perform query and lookup/cross-reference against a range of values

[Siew_WeiGoh2]

Hi,

I am setting up my form with pre-filled data. One of these fields need to be automatically populated depends on selection from previous question.

For example, if I have Stations at xx km location along a specific line, I want to be able to query against more than just one column from the CSV dataset.  I am not sure of how to combine pulldata with IF and AND functions or to query a number between a range of values.

Example of the dataset and the results I want to be returned... Say my CSV dataset contains the following fields:

and I have stations which are at measured kms along its specific line, I want to be able to return the value from Column E based on the criteria that satisfy columns B, C and D above.

Can this be performed in the XLS form?

The alternative is using Cascading Select, which is not exactly ideal.

Any thoughts?

Thank you

SW

[JohnathanHasthorpe]

Hi Siew Wei

You can use pulldata to do this (kind of.....). Pulldata can only do a lookup against a single field in the .csv (not multiple fields), so you will need to create a new version of the .csv that contains a concatenated version of columns B, C, D. The .csv would need to contain rows for all possible combinations of B, C and D - and this is the bit that may be a showstopper for you as you are dealing with lengths.

E.g of concatenated string:

20_80_AAA

Then the pull data function in S123 would look something like the following

 pulldata(<csvfile>, <returnColumn>, <lookupColumn>, <lookupValue>).

The  <lookupValue> would be populated with a concat statement that would combine the results of the appropriate questions e.g.:

concat(${Start},'_',${End}, '_',${Line}) 

Please let me know if you need any further clarification.

Cheers

John

[Siew_WeiGoh2]

Hi John,

Creating a combinations of B,C and D is one way of creating a unique entry. However, how is it going to work when my asset falls between the Start and End of the concatenated field? Will this string be able to query a number between a range of values?

In your example, if I have asset sitting at 30.5, how would it be able to recognize that “20_80_AAA” is the referred pulldata, rather that returning the first entry which would be incorrect?

Thanks again

SW

[JohnathanHasthorpe]

Hi Siew

My description above would not account for ranges - and that is why it is not ideal. Another option would be to use if statements to assign the correct ID. Could you share your data with me and I can see whether this option would be viable? 

Thanks

John

[Siew_WeiGoh2]

Thanks John. Will email you directly.