Verifing Digits with repeat

[eduardo_dimperio]

Hi,

I need to check  a field for that I  fill with  zeros until get length equal to 11 and make the first verification, and that works just fine, but after  that I need a second verification and I need to fill this fields until reach 14 digits and for some reason the same command doesn't work. I tried other transformations but with the same result.

Thank you

Ex.

Input

1234

Output/Next Input

00000001234

Next Output

00000000001234

LAYOUT:
LOAD
    '0'& Instalacao AS ID_INSTALACAO,
    If(len("CPF/CNPJ")<11,Repeat(0, 11-Len("CPF/CNPJ"))&Text("CPF/CNPJ"),Text("CPF/CNPJ")) AS CPF_CNPJ,
    Segmento
FROM [MyLocal.xlsx]
(ooxml, embedded labels, table is Plan1);

AUX_LAYOUT_CNPJ:
LOAD
DISTINCT
    ID_INSTALACAO,
    If(len(CPF_CNPJ)<14,Repeat(0, 14-Len(CPF_CNPJ))&Text(CPF_CNPJ),Text(CPF_CNPJ)) AS CPF_CNPJ,
    Segmento
RESIDENT LAYOUT

//Tried this just for test
NoConcatenate
LAYOUT1:
LOAD
    ID_INSTALACAO,
    Num(CPF_CNPJ) AS CPF_CNPJ,
    Segmento
Resident LAYOUT;
DROP TABLE LAYOUT;

[rubenmarin · Accepted solution]

Hi, for some reason it fails when you use the same name as the field on LAYOUT table, try renaming it, like

LAYOUT:
LOAD
    '0'& Instalacao AS ID_INSTALACAO,
    If(len("CPF/CNPJ")<11,Repeat(0, 11-Len("CPF/CNPJ"))&Text("CPF/CNPJ"),Text("CPF/CNPJ")) AS tmpCPF_CNPJ,
    Segmento
resident Data;

AUX_LAYOUT_CNPJ:
NoConcatenate
LOAD
DISTINCT
    ID_INSTALACAO,
    If(len(tmpCPF_CNPJ)<14,Repeat('0', 14-Len(tmpCPF_CNPJ))&Text(tmpCPF_CNPJ),Text(tmpCPF_CNPJ)) AS CPF_CNPJ,
    Segmento
RESIDENT LAYOUT
;

 Btw, I think you don't need to check the If(), just Repeat('0', 14-Len(tmpCPF_CNPJ))&Text(tmpCPF_CNPJ) will return the same result.

[eduardo_dimperio]

Hi Ruben,

That work, but why it happened?

Will be complicate  to change the field's name every time that we change something in that field. But thanks a lot for that solution.

[rubenmarin]

Hi Eduardo, I don't know, the syntax was correct so I did some test and found that it worked changing field names, I think this is some kind of bug.