Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Load the field using the text() function.
text(PartNo) as PartNo
-Rob
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