topic Re: Adding RecNo() into Table in QlikView

Adding RecNo() into Table

Anonymous — Thu, 13 Sep 2018 11:16:05 GMT

Good morning QV Experts!

I'm trying to find in which order records are loaded into table by using "RecNo()".

When I put "RecNo()" as comment I have about 5000 rows which is correct.

Base:

load

ListNr,

OrderPoItem,

Planned_Res,

PO_DeliveryDate

//RecNo() <-- as comment

Resident MDTB

Order By ListNr asc,ListPos asc;

When I delete comment I have over 22 milions rows which is not correct.

Base:

load

ListNr,

OrderPoItem,

Planned_Res,

PO_DeliveryDate,

RecNo()

Resident MDTB

Order By ListNr asc,ListPos asc;

drop Table MDTB;

Do you know why numbers of rows increased?

Thanks in advence!

Re: Adding RecNo() into Table

Miguel_Angel_Baeyens — Thu, 13 Sep 2018 14:56:57 GMT

Not sure what you mean by number of rows increased, a RESIDENT load will take, if there is no WHERE clause of any type, all the rows from the previous table.

Do the Base and MDTB have the same fields? If so, the RESIDENT load is incrementing the rows in the MDTB table, and the Base table will be automatically concatenated to the previous one (MDTB). Maybe that's why the number increases?

Also, if you are using RecNo() without "AS" in any other table, which is technically possible, you are associating those two tables.

See about RecNo() and RowNo() here https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/CounterFunctions/RecNo.htm

Re: Adding RecNo() into Table

captain89 — Thu, 13 Sep 2018 15:50:55 GMT

Hi,

you had probably made a bad join with synthetic key that duplicates your rows.

Try using the "AS" clause as Miguel suggests.

Re: Adding RecNo() into Table

MarcoWedel — Thu, 13 Sep 2018 20:08:07 GMT

where do you read the number of rows 5000 vs. 22million from (table viewer / status bar)?

Re: Adding RecNo() into Table

Anonymous — Tue, 18 Sep 2018 09:19:28 GMT

Hi,

There, after loading script and insert table.

Re: Adding RecNo() into Table

Anonymous — Tue, 18 Sep 2018 09:21:08 GMT

Thanks Miguel,

As you said, synthetic key caused that issue.

Problem solved.

Re: Adding RecNo() into Table

Anonymous — Tue, 18 Sep 2018 09:21:33 GMT

Thanks Miguel,

As you said, synthetic key caused that issue.

Problem solved.

Re: Adding RecNo() into Table

Miguel_Angel_Baeyens — Tue, 18 Sep 2018 09:37:08 GMT

Glad it worked. Thanks for letting us know