Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Customers, Partners & Luminaries only: You're invited to a Data Analytics Roadmap session. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

Create a QVD column with a 4-digit number sequence

Create a QVD column with a 4-digit number sequence

Good afternoon,

I'm having a problem, I'm not able to create a column on qvd with sequential 4 digit numbers.

Example: Sequential column "0001", "0002" and so on, but I need to put an IF in another column if the CNPJ is the same, the sequence number also needs to be the same.

Could someone help me with this?

5 Replies
Highlighted
MVP & Luminary
MVP & Luminary

Maybe the following is helpful: Counters in the Load.

- Marcus

Highlighted

Hi, with "Text(Num(RowNo(), '0000'))" you can create the codes.

You will need a mapping table using the CNPJ to reuse assigned codes, and applymap (or join) to the data table to add the codes, something like:

setCodes:

LOAD CNPJ as chkCNPJ,

     Text(Num(RowNo(), '0000')) as Code

Resident tempDataTable where not exists(chkCNPJ, CNPJ);


mapCodes:

Mapping LOAD * resident setCodes;


DROP Table setCodes;


Data:

LOAD Fields,

     applymap('mapCodes', CNPJ) as Code

Resident tempDataTable


DROP tempDataTable ;

Highlighted
MVP & Luminary
MVP & Luminary

Num(AutoNumber(CNPJ),'0000') as Sequence


-Rob

Highlighted

Hi Rob, great answer.

Besides this particular question, there is a way of using similar approach with incremental loads?

Highlighted
MVP & Luminary
MVP & Luminary

Hi Ruben, Yes it possible. But because AutoNumber() values are only consistent within a reload, you have to "prime" the autonumber pool by reprocessing the QVD values first, in sequence of autonumber. Something like:

Temp1:

LOAD DISTINCT CNPJ, Sequence

FROM the.qvd (qvd);

Temp2:

LOAD AutoNumber(CNJP) as Sequence

RESIDENT Temp1 ORDER By Sequence;

DROP TABLES Temp1, Temp2;

// You can now safely load and autonumber the new rows.


-Rob

http://masterssummit.com

http://qlikviewcookbook.com

http://www.easyqlik.com