Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

AutoNumber & RowNo

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

1 Solution

Accepted Solutions
marcus_sommer

The reason for the not working right join is that the Key is different - one time only the autonumber from Case_ID and within the other table from the autonumber of Case_ID and the From-To-fields. I think you want to avoid any duplications with your autonumber-key because the tables are 1:n related.

I think there would be really ways to create such a key but it will be probably easier to map your values instead of joining them: Don't join - use Applymap instead.

- Marcus

View solution in original post

20 Replies
vishsaggi
Champion III
Champion III

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;

Not applicable
Author

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
MVP
MVP

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?

Not applicable
Author

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

Not applicable
Author

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
Not applicable
Author

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!!