Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Text Field with Index Numbering by Group

I'm trying build a table similar to the following. I have everything built except for the Name field which is supposed to be a concatenation of the Type field plus an incremented number. The incremented numbering should reset to 001 on each grouping of Project # & Type.

The question is...How do process the existing table containing Project #, Type, Variable Date and Amount to include the Name field with the incremented numbering scheme?

I'm building this within the script of our data model. This is not being built on the front-end application.

NOTE: This table has no relevant usage within QV. We're trying to build a temporary table for export to .csv format for use in another application.

2016-09-16_9-49-30.jpg

1 Solution

Accepted Solutions
sunny_talwar

Try this:

Table:

LOAD Project.ID,

     Transaction.Type,

     Transaction.Type & ' - ' & Num(AutoNumber(RowNo(), Project.ID&Transaction.Type), '000') as Name,

     Transaction.Date,

     Transaction.Amount

FROM

Transactions.xlsx

(ooxml, embedded labels, table is sfTransactions);


Capture.PNG

View solution in original post

7 Replies
MindaugasBacius
Partner - Specialist III
Partner - Specialist III

Please share the data file.

Not applicable
Author

Unfortunately, I cannot share the script because it contains confidential data. Some of it is fairly complex as well.

Basically, the screen shot below is the table that I have built and I need to add the Name column with the incremental numbering (previously shown). So I need to figure out how a load statement, or something else, be written to accomplish this?

2016-09-16_13-32-53.jpg

sunny_talwar

All mindaugasbacius‌ and all of us are looking for is an Excel version of the above snapshot. We won't be able to load the image and it would be an hassle to type all this up and load it.

Not applicable
Author

I've attached the Excel file containing the Transactions table that I've built so far.

Appreciate any guidance that can be provided.

sunny_talwar

Try this:

Table:

LOAD Project.ID,

     Transaction.Type,

     Transaction.Type & ' - ' & Num(AutoNumber(RowNo(), Project.ID&Transaction.Type), '000') as Name,

     Transaction.Date,

     Transaction.Amount

FROM

Transactions.xlsx

(ooxml, embedded labels, table is sfTransactions);


Capture.PNG

sunny_talwar

Or this if you want the date to be ordered and somehow your data is not ordered by date correctly

Table:

LOAD Project.ID,

    Transaction.Type,

    Transaction.Date,

    Transaction.Amount

FROM

Transactions.xlsx

(ooxml, embedded labels, table is sfTransactions);

FinalTable:

LOAD *,

  Transaction.Type & ' - ' & Num(AutoNumber(RowNo(), Project.ID&Transaction.Type), '000') as Name

Resident Table

Order By Project.ID, Transaction.Type, Transaction.Date;

DROP Table Table;

Not applicable
Author

That worked perfectly! Never thought of using the AutoNumber function is that way before.

You're help is sincerely appreciated!