Replacing row over partition by function excluding nulls

cpetti04 — Tue, 16 Jul 2019 21:12:38 GMT

I am struggling with replacing a sql server row_number over partition by function in qlikview. I have fortunately replaced the function from help on the forum from another person's post with combination of the previous and peek function however because I am doing this in a load statement from resident table I can't seem to exclude the nulls. I tried with where clause of load statement however it still counts those row as number 1 just not showing them so seems as if order starts at 2 or 3 depending on number of nulls. Below is piece of script I am using. What I need is it to not even look at nulls in the time_arrivedatscene field. Unfortunately I cannot exclude the nulls in the original SQL as the business units need to know for other reporting requirements when that field is null on an incident.

LEFT JOIN (TestTable)
LOAD
Recno,
if(master_incident_number=previous(master_incident_number),peek("SceneArrivalOrder")+1,1) as "SceneArrivalOrder"
Resident TestTable
Order By master_incident_number, time_arrivedatscene;
DROP Field Recno;

Re: Replacing row over partition by function excluding nulls

Brett_Bleess — Thu, 08 Aug 2019 21:37:02 GMT

If you can attach your app, or a sample app, I think you are more likely to get some help, this one is a bit tough to get your head wrapped around without it I think. That way folks can see the data model and the chart expressions and setup...

Regards,
Brett

topic Replacing row over partition by function excluding nulls in QlikView

Replacing row over partition by function excluding nulls

Re: Replacing row over partition by function excluding nulls