AutoNumber & RowNo

[#1]

Hello,

When I use AutoNumber() combine with RowNo() fuction together, my app broke down. I even cannot load the second "Route" table as I cannot went through the first load "Final_Inital" statement. This way works really well in a small set of data table. But I have hundred of thouands data in my table. It broke down the app and my computer.

Final_Initial:
Load
Case_ID,
[Receive Date]
AutoNumber (RowNo(),Case_ID) as Key2
Resident CASE_TEMP;

Route:
LOAD
Case_ID,
FR_STATE_ID,
TO_STATE_ID,
if(len(trim(ROUTE_DATE))>0, ROUTE_DATE) as Route_Date,
AutoNumber(RowNo(),%CASEIDKey&FR_STATE_ID&TO_STATE_ID) as Key
FROM ...

Does anyone know the reason or is there an alternative approch similar as the one shows above?

Thanks in advance,

Becky

[vishsaggi]

Did you try preceding load:

Final_Initial:

LOAD *,

         AutoNumber (Key2, Case_ID) AS Key;
Load
Case_ID,
[Receive Date]
RowNo() as Key2
Resident CASE_TEMP;

[#3]

Thanks Vish. This is not working as well. It counted the RowNo() so slow, and then stuck at some point forever...There is no issue with the AutoNumber(). Only for the RowNo() fuction.

[marcus_sommer]

There is no need to add a rowno() within the autonumber and if needed (by using more than one different autonumbers within one script) the second parameter needs to be a string. Your script should be look more like:

Final_Initial:
Load
Case_ID,
[Receive Date]
AutoNumber (Case_ID) as Key
Resident CASE_TEMP;

Route:
LOAD
Case_ID,
FR_STATE_ID,
TO_STATE_ID,

ROUTE_DATE as Route_Date
//if(len(trim(ROUTE_DATE))>0, ROUTE_DATE) as Route_Date, // if there is no value you didn't need to assign NULL (missing else-part)
AutoNumber(Case_ID) as Key
FROM ...

whereby I think that is logically not the best way and I suggest to use intervalmatch to resolve your FR-TO-fields, see: https://community.qlik.com/blogs/qlikviewdesignblog/2013/04/04/intervalmatch.

- Marcus

[swuehl]

I assume you want to create a counter per Case_ID in your first load, i.e. resetting the counter on each new Case_ID?

You can create similar without autonumber() using something like

Final_Initial:
Load
Case_ID,
[Receive Date]
If(Peek(Case_ID) = Case_ID. Rangesum(Peek(Key2),1) , 1) as Key2
Resident CASE_TEMP

ORDER BY Case_ID;  // Maybe also oder by date?

[#6]

Hi Marcus,

Thank you a lot for taking into this.

I have limited knowledge for Intervalmatch. I checked it and seems its good for mapping of dates to periods or records? If it's only good at date, then probably not a good approach to my case.

I posted this one becuase of the problems I encountered from this postGeneric Load. It will be great if you have time to take a peek and let me know your thoughts.

Best,

Becky

[#7]

Hi Stefan,

I posted this one based on the problem I encountered in Generic Load. Sunny gave me the idea to do it as this way. It works perfectly fine in his sample solution and I thought it was a great idea. However, this solution just cannot apply on the fact table I am working on. Because the RowNo() count forever and broke down my app. If you have time to take a look the original post, I appreacite that you can share with me some of your thoughts.

Thank you,

Becky

[sunny_talwar]

Do you have a preceding load in your actual application? May be you want to use RecNo() instead of RowNo() and see if that takes you anywhere?

[marcus_sommer]

If I look again on your example I think you are right and you couldn't use intervalmatch because your FR-TO id's are keys to places and not numeric intervals.

Did you yet experience any performance (or other logical) issues when you simply use AutoNumber (Case_ID) as Key?

- Marcus

[Anonymous]

Hi Yun,

Why are you using RowNo with autonumber? What happen if you have same CaseID?

I think that you have to use this:

AutoNumber (Case_ID) as Key2

AutoNumber(Case_ID,%CASEIDKey&FR_STATE_ID&TO_STATE_ID) as Key

Regards!!