Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
kev6brown
Partner - Creator
Partner - Creator

Limiting the values that are loaded in

Hi

 

I have the following load statement but I would like to give the end user some control over what is loaded in. So I would like an inputs box based around the [Fixture Type] field. There are 7 options for this field, Prem, 1, 2, 3, 4, Cup and Other. How can I make it so the end user can choose which [Fixture Type] they load into the dashboard. For the inputs box would i need 7 variables for each [Fixture Type] stating yes or no as to which types the user would like loaded?

 

LOAD Date,
[Home Team],
[Away Team],
[Fixture Group],
Question,
Answer,
[Marking Team],
[Answer Relates To Team],
Respect,
F10
FROM
[C:\Users\Sporting Points - 14-04-22.xlsx]
(ooxml, embedded labels, table is [teamMarkDetailedDownload (2)]);

 

Thanks

 

Kev

Labels (4)
1 Solution

Accepted Solutions
Andrei_Cusnir
Specialist
Specialist

Hello,

 

If my understanding is correct, you have many records with different value for [Fixture Group]. You then want to specify an list of values and allow the records to be loaded only if the value match any of the value provided in the list. 

 

For this you will have to add another part to your script which is the clause "WHERE". An example would like:

LOAD 

    ...
[...]
(...)

WHERE [Fixture Group]='1' or [Fixture Group] = 'Cup';

 

This will load only the records from the source where [Fixture Group] matches '1' or 'Cup'. If you want to have the user only provide a list of values to be checked instead of writing the entire clause, then you can automate the procedure such as:

 

//Table that will hold the values of the options that you want to include in the criteria:
Options:
Load * inline [
Parameters
1
2
Other
];

 

//Variable that will have at the end the SQL part of the statement:
Let varOptions = '';

 

//Iterate through the values and add them to the variable:
For each option in FieldValueList('Parameters')
Trace Processing: $(option);
   Set varOptions = $(varOptions) or Option='$(option)';
Next;

 

//After the variable creation the output will have an initial " or " at the beginning
//Remove it so it won't cause any error
Let varOptions = Right('$(varOptions)', Len('$(varOptions)') - 3);

Trace Loaded options: $(varOptions);

 

//Load the data with the criteria specified in the created variable:
LOAD
   Data,
   Option,
   Value
FROM [...]
(...)
WHERE $(varOptions);

 

This script will create a variable with all the WHERE clause's options and then will use the variable in the WHERE clause itself. So the user will have to only hardcode the values in the first table "Options" and the script will do the rest. You can use the logic for your own dataset and modify the script accordingly.

 

My initial dataset:

 

After loading the script above:

 

I hope that this information was helpful. In case I have misunderstood the use case scenario, please elaborate in details by providing additional information. However, if it has helped you resolve the issue, please mark it as accepted solution to give further visibility to other community members. 
 

Help users find answers! Don't forget to mark a solution that worked for you! 🙂

View solution in original post

3 Replies
Andrei_Cusnir
Specialist
Specialist

Hello,

 

If my understanding is correct, you have many records with different value for [Fixture Group]. You then want to specify an list of values and allow the records to be loaded only if the value match any of the value provided in the list. 

 

For this you will have to add another part to your script which is the clause "WHERE". An example would like:

LOAD 

    ...
[...]
(...)

WHERE [Fixture Group]='1' or [Fixture Group] = 'Cup';

 

This will load only the records from the source where [Fixture Group] matches '1' or 'Cup'. If you want to have the user only provide a list of values to be checked instead of writing the entire clause, then you can automate the procedure such as:

 

//Table that will hold the values of the options that you want to include in the criteria:
Options:
Load * inline [
Parameters
1
2
Other
];

 

//Variable that will have at the end the SQL part of the statement:
Let varOptions = '';

 

//Iterate through the values and add them to the variable:
For each option in FieldValueList('Parameters')
Trace Processing: $(option);
   Set varOptions = $(varOptions) or Option='$(option)';
Next;

 

//After the variable creation the output will have an initial " or " at the beginning
//Remove it so it won't cause any error
Let varOptions = Right('$(varOptions)', Len('$(varOptions)') - 3);

Trace Loaded options: $(varOptions);

 

//Load the data with the criteria specified in the created variable:
LOAD
   Data,
   Option,
   Value
FROM [...]
(...)
WHERE $(varOptions);

 

This script will create a variable with all the WHERE clause's options and then will use the variable in the WHERE clause itself. So the user will have to only hardcode the values in the first table "Options" and the script will do the rest. You can use the logic for your own dataset and modify the script accordingly.

 

My initial dataset:

 

After loading the script above:

 

I hope that this information was helpful. In case I have misunderstood the use case scenario, please elaborate in details by providing additional information. However, if it has helped you resolve the issue, please mark it as accepted solution to give further visibility to other community members. 
 

Help users find answers! Don't forget to mark a solution that worked for you! 🙂
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Do you need to actually what is loaded?  Can you meet the business requirement by providing a listbox for [Fixture Type] and let the Qlik associative filtering take care of limiting the data?

-Rob

kev6brown
Partner - Creator
Partner - Creator
Author

The number of rows is massive, into the millions. The idea is to reduce the amount of data in the dashboard. Different clients will have different fixture types, some will have all 7, some only 3 or 4