Leading zeroes are removed from values (but not everytime)

[#1]

Hello!

I am adding lines to the QV-table. And I want to add only lines where the composite key is not yet in the table.

The key is build like this:

SET MERGEKEY3= upper(text(X)) & '|' & upper(text(ih_site)) & '|' & ih_nbr & '|' & ih_inv_nbr & '|' & upper(text(ih_bill)) & '|' & upper(text(ih_cust));

And loading from (Progress) database is done like this:

Concatenate(invoice_lines_from_source_systems_temp)
LOAD
upper(text(X)) as ih_domain,
upper(ih_site) as ih_site,
ih_nbr,
ih_inv_nbr,
upper(text(ih_cust)) as ih_cust,
upper(text(ih_bill)) as ih_bill,
$(MERGEKEY3) as MERGEKEY3; //composite-key for merge with exists
SQL SELECT
'$(DomainString)' as X,
a.ih_site,
a.ih_nbr,
a.ih_inv_nbr,
a.ih_cust,
a.ih_bill,
FROM PUB.ih_hist a join PUB.idh_hist b on (a.ih_site = b.idh_site and a.ih_inv_nbr = b.idh_inv_nbr and a.ih_nbr = b.idh_nbr)
where Year(a.ih_inv_date) >2003
;

Composite key's (named as MERGEKEY3) leading zeroes are always in place with columns ih_cust and ih_bill, and this is just how it should work because in the SET line is upper(text()) around these columns.This works 100% ok.

But weird things happends when loading data with sql select. Then leading zeroes (in qv-table) are sometimes in place, and sometimes they are dropped off with columns ih_cust and ih_bill. And then later in the script when i check with exists-function is KEY value already in table, I insert lines to the table when it should not be inserted. Because the KEY is with the leading zeroes and inserted line does not have them.

Have you always succeeded with text() function to keep leading zeroes?

Version is 9.00 SR2

Yours,

Harri

[#2]

Hi Harri,

I can't solve your problem directly, because it is not clear which component is doing the strange behaviour. Perhaps SQL or Progress engine or your ODBC-driver is "optimizing" and cuts the leading 0s.Or even the text()-Function of QV (but I dont think so).

Perhaps my workaround can help you:

LEFT('000000', 6 - len(ih_cust)) & ih_cust AS ih_cust

creates a 6-char ih_cust with leading nulls.

regards

Roland

[Miguel_Angel_Baeyens]

Hello Harri,

Is there any chance that those leading zeroes are not in the data pulled from database, or rather, they are present in some fields but not in some others?

If you want a field length 10 and you know it should always have those leading zeroes, you can use the text() function with the repeat() and num() function:

Text(Repeat('0', 10 - Len(Num(ih_bill))) & Num(ih_bill)) As ih_bill

I'm using Num() in the example above to always create those leading zeroes, regardless the field has already them. Num() will return the number without them, so the key will be created correctly.

Hope that helps

[#4]

Hello Minguel,

I agree with you to have a more automatic solution with the repeat(). But I can't imagine a case in which you really need the num(). Wether there are leading nulls or not , len() should always count correct. (I hope so !). Or do you have an counterexample ?

Thanks

Roland

[Miguel_Angel_Baeyens]

Hello Roland,

Not a counterexample, however, since QlikView doesn't have datatypes, if it understands that ih_bill field is text, will keep the zeroes, while using a num() you force to interpret it as numerical. If there are not alphabetical characters, it will work fine anyway.

Regards.