topic Re: Order By with ApplyMap in QlikView

Order By with ApplyMap

quilldew — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I'm just really looking for confirmation that this isn't possible. I receive an error when I try to ORDER BY (ApplyMap(......

I use the mapping within the actual table I am attempting to order, and the mapping works perfectly, and I can also order by the field that is used in the mapping call, but I can't seem to order by the actual value that is brought in.

Is this possible?

Re: Order By with ApplyMap

Anil_Babu_Samineni — Wed, 21 Jun 2017 08:28:13 GMT

Can you do order by for Second table where you are using ApplyMap() with Left Join

Re: Order By with ApplyMap

quilldew — Wed, 21 Jun 2017 08:31:28 GMT

I could do, but I'm using the mapping table so I don't have to join them.

Re: Order By with ApplyMap

Anil_Babu_Samineni — Wed, 21 Jun 2017 08:33:15 GMT

That's how i clearly written use the second table where it's not the Mapping table

Re: Order By with ApplyMap

sunny_talwar — Wed, 21 Jun 2017 08:33:39 GMT

Can you share the script which is giving you the error?

Re: Order By with ApplyMap

quilldew — Wed, 21 Jun 2017 09:18:24 GMT

Hi Sunny,

The whole script is rather large but this I can share this part that is causing an issue.

MAP_CLIENTS:

Mapping LOAD [KEY ClientReferral], [KEY Client]

Resident sub_........;

LOAD [KEY ClientReferral], ReferralInterval,

If(ReferralInterval < 29 and ReferralInterval >= 0,1, 0) as IntervalFlag

;

LOAD [KEY ClientReferral],

If (ApplyMap('MAP_CLIENTS', [KEY ClientReferral]) = ApplyMap('MAP_CLIENTS', Previous([KEY ClientReferral])), ReferralReceivedDate - Previous(ReferralDischargeDate), 100) as ReferralInterval

Resident tbl_.......

ORDER BY ApplyMap('MAP_CLIENTS', [KEY ClientReferral]), ReferralReceivedDate;

And this is the error I receive is:

Field not found

LOAD [KEY ClientReferral],

If (ApplyMap('MAP_CLIENTS', [KEY ClientReferral]) = ApplyMap('MAP_CLIENTS', Previous([KEY ClientReferral])), ReferralReceivedDate - Previous(ReferralDischargeDate), 100) as ReferralInterval

Resident tbl_.......

ORDER BY ApplyMap('MAP_CLIENTS', [KEY ClientReferral]), ReferralReceivedDate

The is no error if I use:

ORDER BY [KEY ClientReferral], ReferralReceivedDate;

but obviously the Order is wrong so my IntervalFlag isn't being applied in the way I would like.

Re: Order By with ApplyMap

sunny_talwar — Wed, 21 Jun 2017 11:11:21 GMT

Not really sure, but you can break it down into two loads

MAP_CLIENTS:

Mapping LOAD [KEY ClientReferral], [KEY Client]

Resident sub_........;

TEMP_TABLE:

LOAD [KEY ClientReferral],

ApplyMap('MAP_CLIENTS', [KEY ClientReferral]) as TEMP_FIELD

If (ApplyMap('MAP_CLIENTS', [KEY ClientReferral]) = ApplyMap('MAP_CLIENTS', Previous([KEY ClientReferral])), ReferralReceivedDate - Previous(ReferralDischargeDate), 100) as ReferralInterval

Resident tbl_.......;

FINAL_TABLE:

LOAD [KEY ClientReferral], ReferralInterval,

If(ReferralInterval < 29 and ReferralInterval >= 0,1, 0) as IntervalFlag

Resident TEMP_TABLE

ORDER BY TEMP_FIELD, ReferralReceivedDate;

DROP Table TEMP_TABLE;

Re: Order By with ApplyMap

quilldew — Thu, 22 Jun 2017 12:56:50 GMT

this throws an error because:

ORDER BY TEMP_FIELD, ReferralReceivedDate;

ReferralReceivedDate isn't in the TEMP_TABLE or FINAL_TABLE.

When removed it gives exactly the same results as the original script with the preceding load.

Re: Order By with ApplyMap

sunny_talwar — Thu, 22 Jun 2017 13:06:14 GMT

May be bring that in

MAP_CLIENTS:

Mapping LOAD [KEY ClientReferral], [KEY Client]

Resident sub_........;

TEMP_TABLE:

LOAD [KEY ClientReferral],

ApplyMap('MAP_CLIENTS', [KEY ClientReferral]) as TEMP_FIELD

If (ApplyMap('MAP_CLIENTS', [KEY ClientReferral]) = ApplyMap('MAP_CLIENTS', Previous([KEY ClientReferral])), ReferralReceivedDate - Previous(ReferralDischargeDate), 100) as ReferralInterval,

ReferralReceivedDate

Resident tbl_.......;

FINAL_TABLE:

LOAD *,

If(ReferralInterval < 29 and ReferralInterval >= 0,1, 0) as IntervalFlag

Resident TEMP_TABLE

ORDER BY TEMP_FIELD, ReferralReceivedDate;

DROP Table TEMP_TABLE;