Order By with ApplyMap

[quilldew]

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?

[Anil_Babu_Samineni]

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

[quilldew]

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

[Anil_Babu_Samineni]

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

[sunny_talwar]

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

[quilldew]

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.

[sunny_talwar]

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;

[quilldew]

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.

[sunny_talwar]

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;