Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
cancel
Showing results for 
Search instead for 
Did you mean: 
aritting
Contributor III
Contributor III

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

 

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?

 

1 Solution

Accepted Solutions
rwunderlich
MVP & Luminary
MVP & Luminary

Load the field using the text() function. 

text(PartNo) as PartNo

-Rob

View solution in original post

3 Replies
Anil_Babu_Samineni

Are you using date format by any chance?
Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
rwunderlich
MVP & Luminary
MVP & Luminary

Load the field using the text() function. 

text(PartNo) as PartNo

-Rob

View solution in original post

aritting
Contributor III
Contributor III

 

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