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

[sunny_talwar]

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.

[marcus_sommer]

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

[#4]

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.

[marcus_sommer]

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

- Marcus

[#6]

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

[#7]

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

[effinty2112]

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

[Anonymous]

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!

[marcus_sommer · Accepted solution]

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

[#11]

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