Two different strings converted into the same value - not desired!

aritting — Fri, 25 Jan 2019 21:57:02 GMT

we have a two part numbers that are getting combined into one part number depending upon which part is loaded first.

The two part numbers are "2012-02-04" and "40493"

if 40493 is loaded first then both parts are displayed in qlik as 40493. if 2012-02-04 is loaded first then both parts are listed at 2012-02-04

This is not wanted!

I'm guessing that qlik is thinking the part number is a date . How do I prevent this unwanted behavior?

Re: Two different strings converted into the same value - not desired!

Anil_Babu_Samineni — Sat, 26 Jan 2019 00:49:36 GMT

Are you using date format by any chance?

Re: Two different strings converted into the same value - not desired!

rwunderlich — Sat, 26 Jan 2019 02:35:40 GMT

Load the field using the text() function.

text(PartNo) as PartNo

-Rob

Re: Two different strings converted into the same value - not desired!

aritting — Tue, 26 Feb 2019 15:24:15 GMT

SO I had an automated routine that takes a parameter as the table name and loads it from source and saves it to qvd. I've added code to use a config file to pass it fields that need to be loaded with the Text() function explicitly

SUB LoadAndStore(vL.TableName);
	trace "####Begin Sub LoadAndStore: $(vL.TableName)";
	                     
	trace 'A CSV file Load.QVS-Config.csv in vG.ConfigPath is required with two fields table_name,field_for_text_load';
	trace 'In the first field, table_name, place the name of the table loaded with this script - exact match';
	trace 'In the second field, field_for_text_load, place the name of the field that needs to be loaded as Text';
	trace '    do not use [] brackets in the CSV file for tables or fields that contain spaces';


	trace 'Begin Text Load CSV from ConfigPath';
	TextLoadCSV:
	LOAD table_name, 
	     field_for_text_load
	FROM '$(vG.ConfigPath)\TextLoad.QVS-Config.csv'
	(txt, codepage is 28591, embedded labels, delimiter is ',', msq);
	//filter to just TrimmedDocName
	TextLoad:
	noconcatenate LOAD table_name, 
	    field_for_text_load
	    resident TextLoadCSV
	    where table_name = '$(vL.TableName)'; //;'$(vL.TrimmedDocName)';
	drop table TextLoadCSV;
	
	//clear variables use int field renaming for TEXT 
	LET vL.TextAddLoadRename =;
	LET vL.TextDropField =;
	LET vL.TextRenameField=;

	LET vL.FieldCount = NoOfFields('TextLoad');
	trace $(vL.FieldCount);
	if vL.FieldCount > 0 then
		trace "TRUE";
		LET NumRows=NoOfRows('TextLoad');
		trace "Num Rows: " $(NumRows);
		FOR i=1 to $(NumRows)
		  LET vL.field_for_text_fuction=FieldValue('field_for_text_load',$(i));
		  trace 'vL.field_for_text_fuction: $(vL.field_for_text_fuction)';
		  LET vL.TextAddLoadRename = '$(vL.TextAddLoadRename)' & ', TEXT([$(vL.field_for_text_fuction)]) AS [$(vL.field_for_text_fuction) TEXT]';
		  LET vL.TextDropField = '$(vL.TextDropField)' & 'DROP FIELD [$(vL.field_for_text_fuction)]; ';	
		  LET vL.TextRenameField = '$(vL.TextRenameField)' & 'RENAME FIELD [$(vL.field_for_text_fuction) TEXT] to [$(vL.field_for_text_fuction)]; ';			 
			vL.field_for_text_fuction=;
		NEXT;
		LET i=;
		LET NumRows=;
		trace '$(vL.TextAddLoadRename)';	
		trace '$(vL.TextDropField)';
		trace '$(vL.TextRenameField)';
	ELSE 
	 trace "not if";
	end if
	LET vL.FieldCount=;

	//Begin Load and Store
	[$(vL.TableName)]:
	// ----------------------------------------
	// Preceding Load Statement
	// ----------------------------------------
	LOAD *
		$(vL.TextAddLoadRename) //, Fields in TextLoad.QVS-Config.csv are loaded with TEXT function
		, '$(vL.TableName)' as [^Fact Type]
		, Num#(Date(Today(), 'YYYYMMDD'), '##') as [^DataLoadDateSKey]
	;
	// ----------------------------------------
	// Create SQL select Statement
	// ----------------------------------------
		SQL SELECT $(vL.TableName).*
			, '$(vG.SKeyPrefix)' AS "^Fact Source"
		FROM $(vG.Schema).TB$(vL.TableName) $(vL.TableName);

	//DROP AND RENAME TEXT Fields
	trace 'DROP AND RENAME TEXT Fields';
	$(vL.TextDropField); // Fields in TextLoad.QVS-Config.csv are dropped
	trace 'renaming';
	$(vL.TextRenameField); // Fields in TextLoad.QVS-Config.csv that were loaded with TEXT function are renamed to orig field name

	// ----------------------------------------
	// Write out the QVD to the defined path
	// ----------------------------------------
	STORE [$(vL.TableName)] INTO '$(vG.QVDPath)\$(vL.TableName).qvd' (qvd);
	// *****************************************;
	// Drop table;
	//*****************************************;
	 DROP Table [$(vL.TableName)];
	 trace "Dropped Table $(vL.TableName)"; 
	 trace "#### End Sub LoadAndStore";
	 
	 //-----------------------------------------;
	 // Clean up Variables;
	 //-----------------------------------------;
	LET vL.TableName=;
	LET vL.TextAddLoadRename =;
	LET vL.TextDropField =;
	LET vL.TextRenameField=;
	drop table TextLoad;
end sub

topic Two different strings converted into the same value - not desired! in QlikView

Two different strings converted into the same value - not desired!

Re: Two different strings converted into the same value - not desired!

Re: Two different strings converted into the same value - not desired!

Re: Two different strings converted into the same value - not desired!