Keep leading zeroes

[Fabiano_Martino_Intelco]

Hi,

I'm maintaining an ETL app where a lot of fields have leading zeroes.

If these fields are loaded normally they will lose the leading zeroes, but i need to keep them.

Using the Text() function solves the problem, but in the app there are a lot of fields in this situation.

Is there an alternative to keep the leading zeroes?

Setting a configuration variable in the script would be ideal, but I couldn't find any.

Thanks

[Kushal_Chawda]

@Fabiano_Martino_Intelco  I don't know how it automatically removes leading zero. Mostly Qlik keeps leading zero as is when you load the data. Qlik internally stores '001' as 1 but representation is always actual values which is '001'. As you mentioned it removes leading zero, I don't think there is any other way apart from text() to keep it.

[Fabiano_Martino_Intelco]

Hi @Kushal_Chawda 

when loading values with a numerical meaning Qlik will keep the first textual representation it finds, so 001, 01 and 1 will all be associated with the number 1 and the textual representation 001.

In my situation the values are not numbers, but codes (keys for customer, product and parts) so I need to keep the three values distinct (the customer with code 001 is different from the customer with code 01).

I used the Text() function where I found the problem, but consider that the application is based on many tables with many external keys.

Thanks

[Kushal_Chawda]

@Fabiano_Martino_Intelco  What exactly the problem is with text() function?

[Fabiano_Martino_Intelco]

Hi @Kushal_Chawda 

per se the Text() function is not a problem and in fact it solves the immediate problem.

The question is that I have an articulated ETL application that loads the data with a LOAD *; SQL SELECT * from several tables.

This syntax is very straightforvard and easily maintaied.

Using the Text() function I need to revise all the LOAD statements and introduce the Text() for the fields that need to retain their original values.

Given that some tables can have up to 200 columns this requires some time and attention.

I also have to instruct my colleagues to do the same for their applications, making sure we use the Text() function on the same fields.

Thanks

[Kushal_Chawda · Accepted solution]

@Fabiano_Martino_Intelco  Better option will be to change the data type of columns in ETL table itself to represent it as text so that you can load all the columns normally without having to rely on converting to text.

Other option is you can still use load * like below

LOAD *,
     
     Text(Field1) as Field2;

Select * FROM;



drop Field Field1;


RENAME field Field2 to Field1;
      

[marcus_sommer · Accepted solution]

You may pre-load the field-value interpretation in beforehand, here a simple example to create a generic approach for it:

let i = 0;
for each vField in 'F1', 'F2'
   let i = $(i) = 1;
   let vFields = '$(vFields)' & 'text('001') as ' & [$(vField)] & if($(i) = 1, '', ',');
next

dummy: load $(vFields) autogenerate 1;

t: load *; select * from xyz;

drop tables dummy;

Beside this loading any kind of data with an implicit wildcard-approach may be quite common as a shortcut but it's not best practice it would be the explicit defining of each single field and if needed with applying the necessary transformations and renaming. Helpful for such approaches would be to query at first the data-base system-tables to fetch the existing tables/fields and their data-types and using them to create the appropriate load-statements. 

[marksouzacosta · Accepted solution]

I would try to use Num instead of Text since Text will convert your numerical values to Text.
You can try something like this:

Num(my_field,'0000000000') AS my_field_formatted

However, I know this will not solve your problem since you'll have to change your ETL Load Statements. There is a tricky path to solve that: dynamically write your Load Statements based on the column types. That requires lots of tests and creativity, but it is possible. I did that in multiple scenarios.

Following a code for your reference. The one below consumes a QVD that holds columns definitions and creates a preceding load based on some conditions:

And this is the code that creates the SQLColumns QVD file: