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
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
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
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
Thanks John. Will email you directly.