Grouping with MIN date.

[Anandsankar]

Hi Guys,

I am new to Qliksense and also have minimal Sql knowledge. I have to load a table with transactions for claims with minimum date. I create table t1 which gives around 400K records. From this table, i have filtered the minimum date transactions which gives around 230K records. I would like the second table to have all the columns as in the first table and have only these 230K records loaded in my final table. I am not sure how to do that. The below 2 loads work fine.. not sure how to combine them and return the filtered records with all columns.

Many thanks in advance!!

Regards,

Anand

t1:

LOAD *

SQL SELECT DISTINCT
"clm_number",
"trans_date",
"business_area",
"gross_opening_reserve",
"expsr_creator",
"expsr_adjuster_hist",
"expsr_adjuster_group_hist",
"expsr_adjuster_group_type_hist",
"lossloc_country",
"lossloc_prov"

FROM "CSTP"
where "business_area" = 'Property';

t2:
Left Join (t1)
Load
"clm_number",
min(Timestamp("trans_date"),'MM/DD/YYYY hh:mm:ss') as MinDate
Resident t1 Group by "clm_number";

[Anil_Babu_Samineni]

Until unless you are not doing any drop statement so the filters will come default.

[jonathandienst]

How about:

...
t2:
Inner Join (t1)
Load
"clm_number",
min(Timestamp("trans_date"),'MM/DD/YYYY hh:mm:ss') as MinDate
Resident t1 Group by "clm_number";

[Anandsankar]

Hi Jonty,

Thanks for your reply. I tried the code as you suggested.. however, the query still returns all the records 400K and doesn't filter to show only the min transaction date ones. Please advise.

t1:
LOAD *

// table 1 brings in all transactions for Property

SQL SELECT DISTINCT
"clm_number",
"trans_date",
"business_area",
"gross_opening_reserve",
"expsr_creator"

FROM "CSTP"
where "business_area" = 'Property' 
;

t2:
Inner Join (t1)
Load
"clm_number",
min(Timestamp("trans_date"),'MM/DD/YYYY hh:mm:ss') as MinDate
Resident t1 Group by "clm_number";

[Anandsankar]

Hi Guys,

I haven't been able to make progress with this query. I would like the final dataset to return the 230K records for all the data fields. The data has to be grouped by Claim number and have the minimal date transaction.. So I get only unique claim number in the dataset.

Thanks,

Anand