Keep Leading Zeros when loading from Proc in SQL instead of reading from table

DylanJones — Sat, 16 Nov 2024 21:30:52 GMT

Hi,

My issue is very similar to another post about leading zeros. I have hundreds of different item codes (some with trailing zeros) and QV is combining them due to the trailing zeros.

I've seen this post:

https://community.qlik.com/t5/QlikView-App-Development/Keeping-Leading-Zeros/td-p/883009

which is a very similar issue to what I'm having. The solution here involved using Text() in the loadscript as follows:

LOAD OrderNumber,

     Product

     Text(Product) as NewProduct

SQL SELECT OrderNumber, Product

FROM <Table>;

One issue with this (as far as I know) is that you are listing all the columns in the table to begin with. The proc I am using has 30+ columns and so doing that for every proc called with the Item Code in it would be cumbersome and time consuming.

In my script I'm taking my information from a Proc like this:

"ItemMasterFile": 
	SQL 
	EXEC SP_OUTPUT_GEN_ItemMaster			NULL,	
											NULL;

Is there some way to set the column to a Text() type through the proc without having to list every column every time? I also have lots of procs with the Item Codes in them so they need to link.

Re: Keep Leading Zeros when loading from Proc in SQL instead of reading from table

marcus_sommer — Wed, 30 Jan 2019 11:49:10 GMT

I agree that using a wildcard for loading the fields is very convenient and leads to a clear load-statement but it's not mandatory a best practice approach because changes in the source could cause additional and/or missing fields and might lead to errors and/or wrong results. For the most load-statements you could use a wizard so that there are not really big efforts to create it - and the preceeding part is then just copy & paste + your needed adjustments and applying text() to maybe one or two fields it's not very time-consuming.

Beside this you could use the following approach to define a field as string-field:

Dummy: load text(recno()) as Product autogenerate 1;

YourTable: load *; SQL select * from Source;

drop tables Dummy;

- Marcus

topic Re: Keep Leading Zeros when loading from Proc in SQL instead of reading from table in QlikView

Keep Leading Zeros when loading from Proc in SQL instead of reading from table

Re: Keep Leading Zeros when loading from Proc in SQL instead of reading from table