topic Re: dynamic query in App Development

dynamic query

Loeckli — Thu, 09 Jun 2022 13:13:11 GMT

Hello Everbody

Lets say, I have the following table :

Groups:	Status :	Date1:	Date2:
banana1	ready	09.06.2022	11.06.2022
banana1	not ready	10.06.2022	11.06.2022
banana1	not ready	11.06.2022
banana1	ready	09.06.2022	11.06.2022
banana1	ready	09.06.2022	11.06.2022
banana2	not ready	14.06.2022
banana2	not ready	15.06.2022
banana2	not ready	16.06.2022
banana2	ready	09.06.2022	11.06.2022
banana2	ready	09.06.2022	11.06.2022
banana2	not ready	19.06.2022

I have the column Group, in which there are several entries with the same name as Banana1. As soon as all statuses of a group (e.g. Banana1) are on ready, I want to calculate the average cycle time of the whole group Banana1.

How could this be programmed dynamically ?

Thank you very much in advance 🙂

Re: dynamic query

Or — Thu, 09 Jun 2022 13:21:23 GMT

There's probably a cleaner/better way, but one way of approaching it would be:

YourTable:
Load * INLINE [
Groups, Status, Date1, Date2,
banana1, ready, 09.06.2022, 11.06.2022
banana1, not ready, 10.06.2022, 11.06.2022
banana1, not ready, 11.06.2022,
banana1, ready, 09.06.2022, 11.06.2022
banana2, ready, 09.06.2022, 11.06.2022
banana2, ready, 10.06.2022, 13.06.2022
];

Ready:
Load Groups
Where GroupStatus = 'ready';
Load Groups, if(Only(Status)='ready','ready') as GroupStatus
Resident YourTable
Group By Groups;
Left Join
Load * Resident YourTable;

Drop Table YourTable;

You can then use the Ready resident table to do whatever calculations you want, since it only contains lines for which all statuses for Group are ready. Note that you could also do this by using Exists on the Ready table without left joining the lines if you prefer.

Re: dynamic query

Loeckli — Fri, 10 Jun 2022 11:56:30 GMT

Hi Or

Thank you very much for your response and answer. So far the code works for me.

I am new to Qliksense programming so I just have no idea how to program the calculations for each group the max (date2)- min(date1 ) this dynamically.

Re: dynamic query

Loeckli — Fri, 10 Jun 2022 12:40:21 GMT

I have just seen that the values which are in the Ready table have not been filtered correctly. There are groups which do not have the right Status.