Create a QVD column with a 4-digit number sequence

[triersistemas]

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?

[marcus_sommer]

Maybe the following is helpful: Counters in the Load.

- Marcus

[rubenmarin]

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 ;

[rwunderlich]

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

-Rob

[rubenmarin]

Hi Rob, great answer.

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

[rwunderlich]

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