Add multiple values in Select Field Value by Query Block

[TaMahdi]

Hello,

I'm trying to create an automation to get a straight table from the Qlik Sense app for one year (from Jan- Dec) to an Excel file.

The app has filters based on Year and Month, as such, it always represents the latest month (please refer to the screenshot).

In the Select Field Value by Query 2, I want to include all year months, however, it takes only Jan, I tried to add Feb, but the end result shows only Jan.

My question, how can I use "Select Field Values by Query" block to to select multiple values for a field; in my case to add the months from Jan-Dec.

Can you please advise how can I map the block to add all the months to have them all in one excel file?

Appreciate your support @MarkGeurtsen.

Thanks,

[AfeefaTk]

Hi @TaMahdi 

If I understood correctly you are looking to filter out straight table data of the qlik sense app from the month of Jan to Dec for a particular year and store it in the excel file

I have done this in the below example automation. Please find the attached workspace

Please use the 'list current selections' block after the 'Select Field Value by Query' block to ensure the selections are actually applied

Please note while using the 'Select Field Value by Query' block you will have to map the qText attribute from the list field values block(formula: {$.ListValuesOfField.item.qText}) whereas if you are using the 'Select Field Value' block to apply selections, you will have to map the qNum attribute from the list field values block(formula: {$.ListValuesOfField.item.qNum})

Please let me know if I have misunderstood the issue here and if so please help us with your automation workspace in order to investigate further

Thanks!!

Afeefa TK

[AfeefaTk]

Hi @TaMahdi 
I think you can may be refer to the template which is already available in the template picker

You can find it by navigating to Add new -> New automation -> Search templates and search for 'Copy and update data from a Qlik Sense straight table to an Excel sheet' in the search bar and click on the Use template option in order to use it in the automation

The below articles might help you to get an understanding of the template implementation 

https://community.qlik.com/t5/Knowledge/Qlik-Application-Automation-Copy-data-to-Microsoft-excel-she...

https://community.qlik.com/t5/Knowledge/Copy-and-update-data-to-Microsoft-Excel-sheet-using-automati...

Hope this helps!!

Thanks!!
Afeefa TK

[TaMahdi]

Hello @AfeefaTk 

Thank you very much for your prompt response.

Instead, I've added a condition for each month, for Feb, I've added Variable row (list) - as per the screenshot, but I got an error 400, appreciate it if you advise on any possible reasons that prevent the automation to get Feb values.

Thank you.

[AfeefaTk]

Hi @TaMahdi 

Could please also attach the error message from Add rows to table (batch) block.It could be also useful for us to investigate further if you share the automation workspace json file

Thanks!!
Afeefa TK

[TaMahdi]

Hello @AfeefaTk ,

Please find a screenshot of the automation, I'm using store table in Excel template adding Clear selections, List Field value, Select Field Values by Query and two conditions to specify the year and month then Get Straight table. 

After generating Jan values (A2 - A32), I want to concatenate Feb Value afterwards let say from A34 onwards, but I'm not sure which Excel block to use, whether its  Add Rows to Table (Batch) or Add Row with Cells to Table to specify the number of the row for the next month.

For the Error, I've attached in my previous comment a screenshot of Error 400, where it mentioned that the argument is invalid or missing or has an incorrect format.

[AfeefaTk]

Hi @TaMahdi 

The 'Add Rows to Table (Batch)' block expects an array of rows where we can pass multiple rows within an array and in your automation, the row list variable gives a single array(single row) during each iteration, and this single row is mapped to 'Add Rows to Table (Batch)' block. Hence it gives an error saying the data passed is invalid/has an incorrect format

Could you please try replacing the 'Add Rows to Table (Batch)' block with 'Add Row To Table' from the Excel connector which will create a new row in each iteration of the 'Get straight table data' block

Hope this helps!!

Please let us know if you are still facing any further issues

Thanks!!
Afeefa TK

[TaMahdi]

Hi @AfeefaTk ,

I can't thank you enough for simplifying the concepts here.

In my case, I want to add multiple rows for Feb values (30 rows), what type of variable shall I create?

As I'm getting error 404 when adding 'Add Row To Table' block.

Thanks a lot in advance.

[AfeefaTk]

Hi @TaMahdi 

Are you sure you are using the correct drive id,item id, and worksheet?

Since it gives a 404 resource item not found error, we suspect that any of the above field values are invalid

Please use the "lookup" functionality of drive_id,item_id, and worksheet fields to choose from the available options in order to make sure the values entered are valid

Could you please cross-check and verify?

The variable should be of type list and select the data you want in the correct sequence from the 'Get straight table data' endpoint as in item to the row list variable

Please find the attached json workspace for your reference 

Hope this helps!!

Thanks!!

Afeefa TK