topic AutoNumber & RowNo in QlikView

AutoNumber & RowNo

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: AutoNumber & RowNo

vishsaggi — Wed, 17 Aug 2016 16:24:08 GMT

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;

Re: AutoNumber & RowNo

Wed, 17 Aug 2016 17:05:02 GMT

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.

Re: AutoNumber & RowNo

marcus_sommer — Wed, 17 Aug 2016 17:11:35 GMT

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

Re: AutoNumber & RowNo

swuehl — Wed, 17 Aug 2016 20:30:07 GMT

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?

Re: AutoNumber & RowNo

Wed, 17 Aug 2016 20:38:46 GMT

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

Re: AutoNumber & RowNo

Wed, 17 Aug 2016 20:42:35 GMT

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

Re: AutoNumber & RowNo

sunny_talwar — Thu, 18 Aug 2016 01:05:45 GMT

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?

Re: AutoNumber & RowNo

marcus_sommer — Thu, 18 Aug 2016 05:56:41 GMT

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

Re: AutoNumber & RowNo

Anonymous — Thu, 18 Aug 2016 06:44:22 GMT

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

Re: AutoNumber & RowNo

sunny_talwar — Thu, 18 Aug 2016 07:07:12 GMT

marcus_sommer‌ -

There might be a better way to do this, but the reason for using AutoNumber(RowNo(), Case_ID) vs AutoNumber(Case_ID) was this:

Case_ID	AutoNumber(Case_ID)	AutoNumber(RowNo(), Case_ID)
10002	1	1
10002	1	2
10002	1	3
10002	1	4

I was trying to create an incremental value for each Case_ID (Column 3), instead of creating a single value for each Case_ID (Column 2).

I hope it will clarify the doubts as to why I proposed one vs. the other. Again, I don't know if there is a better approach to do this, but there is definitely a difference between the two expressions.

Re: AutoNumber & RowNo

marcus_sommer — Thu, 18 Aug 2016 07:18:01 GMT

Ah, I understand now which direction is wanted. I would do either AutoNumber (Case_ID & '|' & AnotherKeyField) as Key or a peek-solution like in the suggestion from Stefan (depending on which kind of number/Key is really needed).

- Marcus

Re: AutoNumber & RowNo

Thu, 18 Aug 2016 14:01:11 GMT

Preceding load is a new concept for me. If a preceding load needs to be used with SQL SELECT, or the script as below, then I don't think I have a preceding load.

    LOAD *,
    month(EventTime) as EventMonth,
    year(EventTime) as EventYear,
     ;
    LOAD
    timestamp(timestamp#(mid(@1:n,3,12), ‘MMDDYYhhmmss’)) as EventTime
    FROM ...;

I only include calculations in my Load statement in the Data Model Layer after I built the Transformation Layer.

Load

Case_ID,

month(receive_date) as date,

...

From ....qvd;

I just tried RecNo() which behaved the same way as RowNo(). It stuck there after I started runing my script. There is no problem if I only use AutoNumber() like what Marcus Sommer suggested above. But the last step in the script-- Right Join wasn't work.

Re: AutoNumber & RowNo

marcus_sommer — Thu, 18 Aug 2016 14:06:45 GMT

Which right join? Please post all relevant script-parts.

- Marcus

Re: AutoNumber & RowNo

Thu, 18 Aug 2016 17:50:39 GMT

Hi Marcus,

It took me the whole morning waiting for run the script with AutoNumber(RecNo()/RowNo()......).

Here is the partial code for the RIGHT JOIN. Please find the original script from Sunny T in the attachment.

Right Join (FinalTable)
LOAD Case_ID,
Key2 as Key,
Receive_Date
Resident FinalTable_initial;

In Suny's sample qvw file, it working well. But if I keep the RowNo() within AutoNumber() in my app, I cannot run the script, it dead forever.

My actual app can function well, after I removed the two RowNo(), only keep the AutoNumber(). However the Right Join part wasn't work.

Thanks Marcus.

Becky

Re: AutoNumber & RowNo

Thu, 18 Aug 2016 17:52:37 GMT

Hi Manuel,

Thanks for your great idea. I tried to use the way you suggested yesterday. But part of my app wasn't working. Please see my response to Marcus for more details.

Thank you,

Becky

Re: AutoNumber & RowNo

effinty2112 — Thu, 18 Aug 2016 22:26:02 GMT

Hi yun,

There is a comma missing after the [Receive Date] field in your script. Try it again and let us know.

If that doesn't work try:

Final_Initial:

Load

Case_ID,

[Receive Date],

if(Case_ID<>Peek('Case_ID'),1,RangeSum(Peek('Key'),1)) as Key

Resident CASE_TEMP Order By Case_ID;

Regards

Andrew

Re: AutoNumber & RowNo

Anonymous — Fri, 19 Aug 2016 06:38:07 GMT

Hi Yun,

Is this that you want?

I dont't use Autonumber, i used Date difference between receive_date and max date from route. Then I select only the rows with min difference.

See attached.

Regards!

Re: AutoNumber & RowNo

marcus_sommer — Fri, 19 Aug 2016 07:15:04 GMT

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

Re: AutoNumber & RowNo

Fri, 19 Aug 2016 17:58:35 GMT

Thanks Manuel for your help. This is what I am looking for... to get each date based on each receive_date.