Value based on max date in Qliksense script .

[suvechha_b]

Hi All ,

How to build the script in qliksense to get the value of "GROSS_CLAIM_OUTSTANDING" based on max(CL_ITEM_DETAIL_IMPORT_DATE_END) .

The table is loaded in my model. All the other fields are also mandatory. How to write the script and get the value of GROSS_CLAIM_OUTSTANDING from max  CL_ITEM_DETAIL_IMPORT_DATE_END in my model.

[DWQMV_CLAIM_ITEM_DETAIL]:
LOAD "CLAIM_ITEM_DETAIL_KEY",
"CLAIM_ITEM_KEY",
"CID_CURRENCY_CODE",
"PERIL",
"EXTENT_OF_LOSS",
"ORIGINAL_ESTIMATE",
"ORIGINAL_ESTIMATE_VAT",
"GROSS_CLAIM_OUTSTANDING",
"GROSS_CLAIM_OUTSTANDING_VAT",
"THIRD_PARTY_DAMAGE_ESTIMATE",
"SALVAGE_ESTIMATE",
"SALVAGE_PROVISION",
"THIRD_PARTY_RECOVERY_ESTIMATE",
"RISK_ITEM_ODO_READING",
"CL_ITEM_CATASTROPHE_CODE",
"CL_ITEM_CATASTROPHE_DESCRIPTION",
"ICD_10_PRIMARY_CODE",
"ICD_10_SECONDARY_CODE",
"PRACTITIONER_NAME",
"PRACTICE_NUMBER",
"SERVICE_LOCATION_TYPE",
"AGGREGATE_CLAIM_ITEM_AMOUNT_RECEIVED",
"AGGREGATE_CLAIM_ITEM_AMOUNT_RECEIVED_VAT",
"AGGREGATE_GROSS_CLAIM_ITEM_PAID",
"AGGREGATE_GROSS_CLAIM_ITEM_PAID_VAT",
"CLAIM_ITEM_AUTHORISATION_DATE",
"WEATHER_CONDITIONS",
"ROAD_CONDITIONS",
"CL_ITEM_DETAIL_IMPORT_DATE_BEGIN",
"CL_ITEM_DETAIL_IMPORT_DATE_END"
FROM 'lib://QlikData/Model_ClaimItemDetail.qvd' (qvd);

Thanks in Advance.

[JordyWegman]

Hi Suvechha,

If I understand correctly, do you only want the rows (values) from the CL_ITEM_DETAIL_IMPORT_DATE_END max date?

If you want this do the following:

tmpTable:
Load
    Max(CL_ITEM_DETAIL_IMPORT_DATE_END) as MaxCL_ITEM_DETAIL_IMPORT_DATE_END
FROM 'lib://QlikData/Model_ClaimItemDetail.qvd' (qvd);

Let MaxClItemDetailImportDateEnd = Peek('MaxCL_ITEM_DETAIL_IMPORT_DATE_END,0,[tmpTable])

[DWQMV_CLAIM_ITEM_DETAIL]:
LOAD "CLAIM_ITEM_DETAIL_KEY",
"CLAIM_ITEM_KEY",
"CID_CURRENCY_CODE",
"PERIL",
"EXTENT_OF_LOSS",
"ORIGINAL_ESTIMATE",
"ORIGINAL_ESTIMATE_VAT",
"GROSS_CLAIM_OUTSTANDING",
"GROSS_CLAIM_OUTSTANDING_VAT",
"THIRD_PARTY_DAMAGE_ESTIMATE",
"SALVAGE_ESTIMATE",
"SALVAGE_PROVISION",
"THIRD_PARTY_RECOVERY_ESTIMATE",
"RISK_ITEM_ODO_READING",
"CL_ITEM_CATASTROPHE_CODE",
"CL_ITEM_CATASTROPHE_DESCRIPTION",
"ICD_10_PRIMARY_CODE",
"ICD_10_SECONDARY_CODE",
"PRACTITIONER_NAME",
"PRACTICE_NUMBER",
"SERVICE_LOCATION_TYPE",
"AGGREGATE_CLAIM_ITEM_AMOUNT_RECEIVED",
"AGGREGATE_CLAIM_ITEM_AMOUNT_RECEIVED_VAT",
"AGGREGATE_GROSS_CLAIM_ITEM_PAID",
"AGGREGATE_GROSS_CLAIM_ITEM_PAID_VAT",
"CLAIM_ITEM_AUTHORISATION_DATE",
"WEATHER_CONDITIONS",
"ROAD_CONDITIONS",
"CL_ITEM_DETAIL_IMPORT_DATE_BEGIN",
"CL_ITEM_DETAIL_IMPORT_DATE_END"
FROM 'lib://QlikData/Model_ClaimItemDetail.qvd' (qvd)
Where CL_ITEM_DETAIL_IMPORT_DATE_END = '$(MaxClItemDetailImportDateEnd)'
;

Jordy

Climber