Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Help with a script in qvd with GROUP BY AND HAVING clauses, that uses
Incremental3:
SQL SELECT
There's no question in this question...
How to filter out duplicates in qvd using having and group by clause
I have rows an example of these such rows with details
ID -11 , AGENT_ID -22 , STORE-A , YEAR-2023,AMOUT -2200
ID-11 , AGENT_ID -23 ,STORE -A , YEAR-2023,AMOUT -2200
ID-22 , AGENT_ID -15,STORE-B ,YEAR0- 2020,AMOUNT-100
ID-22 , AGENT_ID -16,STORE-B ,YEAR-2020,AMOUNT-100
how do i write a qvd loading all the columns from a database code to only pick the row with the bigger AGENT_ID
A snip of the dataset
There are multiple ways. Qlik Scripting can use "group by" just like SQL, but the having-clause in SQL is not in qlik, but there are ways around that. You can write your SQL statement in qlik as you started in your post, but are you actually asking how to use "group by" and "having" in SQL?.
Do you want to load only one row - the one where AGENT_ID has the highest value?
Then you say "write to qvd". If you have loaded a table into memory in Qlik Sense you can write it to QVD file using STORE.
I only want to load one row where the central id is the highest
This is one way to do it:
tmp: SQL SELECT ... from ...;
Incremental3: first 1 noconcatenate load * resident tmp order by AGENT_ID desc;
drop table tmp;
(I ued highest AGENT_ID, you wrote "central id" in your last comment so you may have to change that.)