How to combine fields with overlapping dates into one field
Hello, everyone. Since I am new to Qlik Sense, I am not sure, but as I understood it does not work regular functions and some methods as in postgresql.
That's why this question, maybe someone has encountered this. Is it possible in Qlik Sense to combine (compact) many fields with overlapping dates into one field?
Let me explain with an example, let's say I have the following fields: id_client, id_question, date_open, date_close. The table looks like this, taking the id of one client:
the logic is, if the dates of the questions overlap, they should connect and count as one question, but it is considered the next question and does not compact. Tried doing a window function, and then comparing that if the next date_open <= the date close in the first filed, then the dates overlap and output date_close of the second line, but then stalemate and stupor.
The output should be id_client. | id_question| date_open | date_close YZIYR00R | 14534534 | 11.01.2022 | 12.01.2022 YZIYR00R | 36485023 | 13.01.2022 | 15.01.2022
can you please explain your definition of what should be considered as one group, best using some more examples?
What if open dates are not common across all intervals of one group like in your initial example?
Can two intervals still be considered the same group even if they do not intersect each other but instead intersect a common third interval? In other words groups are only delimited by dates that belong to neither interval?
Yes, two intervals can be considered one group, even if they do not overlap each other, but overlap with a common third interval.
My task is as follows, I have a customer who calls the operator with an appeal and I would like, if the dates of their questions overlap, to count it as one appeal That is, the simplest example looks like this:
1 dec | 2 dec | 3 dec | 4 dec | 5 dec
1 | 1 | 1 |null |null
null|null | 2 | 2 | 2
3 | 3 | null|null |null
That is, the client called on December 1, his question was solved until 3. The same client called with another question on December 3, and so on. In example it’d be only one questions in one filed like
This will not work correctly, I made a mistake, it should compare the closing date and the next opening date field
My task is as follows, I have a customer who calls the operator with an appeal and I would like, if the dates of his questions overlap, to combine it into one appeal. That is, I go sequentially by dates and compare the closing date and each following opening date, did I explain clearly?
My task is as follows, I have a customer who calls the operator with an appeal and I would like, if the dates of his questions overlap, to combine it into one appeal. That is, I go sequentially by dates and compare the closing date and each following opening date, so there should be no gaps in the dates. Answering the question -Can two intervals still be considered the same group, even if they don't overlap each other, but instead overlap a common third interval? Here's an example, if I understand you correctly
By implementing the logic between date_close and date_open to form groups, I think it satisfies your requirements. As there are row by row comparison for fields, your data must be sorted beforehand by id_client, date_open and date_close accordingly.
SerhanKaraer, I understood your code and implemented it, but look, if you add a nonsequential date to the table, it will not bind, that is, let's add a row 9, where the dates are (date_open - 11.01 and date_close - 12.01) then this column will not collapse
For this specific case, your source data must be ordered by id_client (asc), data_open (asc), data_close (asc) thus you don't expect such a case you mentioned. Otherwise, there emerge many options to handle for comparing the records.
Besides, there was not a user check between inter records, so comparing data_open and data_close is not enough when there are other clients, so I modified grouping part considering this situation.
