topic Re: Create a QVD column with a 4-digit number sequence in App Development

Create a QVD column with a 4-digit number sequence

triersistemas — Tue, 06 Nov 2018 18:13:58 GMT

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?

Re: Create a QVD column with a 4-digit number sequence

marcus_sommer — Wed, 07 Nov 2018 09:51:06 GMT

Maybe the following is helpful: Counters in the Load.

- Marcus

Re: Create a QVD column with a 4-digit number sequence

rubenmarin — Wed, 07 Nov 2018 12:36:41 GMT

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 ;

Re: Create a QVD column with a 4-digit number sequence

rwunderlich — Wed, 07 Nov 2018 12:52:50 GMT

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

-Rob

Re: Create a QVD column with a 4-digit number sequence

rubenmarin — Wed, 07 Nov 2018 12:56:34 GMT

Hi Rob, great answer.

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

Re: Create a QVD column with a 4-digit number sequence

rwunderlich — Wed, 07 Nov 2018 23:09:53 GMT

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