Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

When Verbatim=1; doesn't work.

I'm importing semi colon delimited data from a text file that has a field that is exactly sixteen characters long usually with extra spaces left on the end.  Those extra spaces are extremely important when I concatenate that field along with others because it forms the central key to the entire database.  I need to concatenate this data into my main table through this script, but the resultant output has no spaces in it.  Here is my script.

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

Set Verbatim=1;

Directory;
Forecast:
$(Include=\\Master\Load Forecast.txt)


Forecast1:
LOAD *,
  date(makedate(left([Week End Date],4),mid([Week End Date],5,2),mid([Week End Date],7,2)),'YYYY/MM/DD') as [Production Date]
Resident Forecast;

DROP Table Forecast;

Concatenate (Forecast1) LOAD  Weekend(date(makedate(left([DUE DATE],4),mid([DUE DATE],5,2),mid([DUE DATE],7,2)),'YYYY/MM/DD'))+7 as [Production Date],
         CST & STYLE & SIZE & LEFT(COLOR,5) as [Customer SKU],
         NUM([OPEN QUANTITY],'#,##0') as [PO Pairs]
FROM
\\Vmi\fcstdata\PO8000.TXT
(txt, codepage is 1252, embedded labels, delimiter is ';', msq);

CONCATENATE (Forecast1) LOAD  Weekend(date(makedate(left([ETA DATE],4),mid([ETA DATE],5,2),mid([ETA DATE],7,2)),'YYYY/MM/DD'))+7 as [Production Date],
         CST & STYLE & SIZE & LEFT(COLOR,5) as [Customer SKU],
         NUM([OPEN QUANTITY],'#,##0') as [PO Pairs]
FROM
\\Vmi\fcstdata\PO8020.TXT
(txt, codepage is 1252, embedded labels, delimiter is ';', msq);


The text files I'm working from (PO8000.TXT and PO8020.TXT) have the correct amount of characters except for COLOR having one extra that I removed via the LEFT function.

Why isn't the SET VERBATIM=1; command working?

Thanks,

Gr8Scott

1 Solution

Accepted Solutions
swuehl
MVP
MVP

Hi gr8scott,

still not sure why the Verbatim is not working, honestly I haven't used it up to now.

As a workaround, you could fill the field with spaces (chr(32)) up to 16 characters in total, like

STYLE&repeat(chr(32),16-len(STYLE)) as STYLE,

CST & STYLE&repeat(chr(32),16-len(STYLE)) & SIZE & LEFT(COLOR,5) as [Customer SKU],

Hope this helps,

Stefan

View solution in original post

4 Replies
swuehl
MVP
MVP

Hi Scott,

is it possible that you upload your two text files or parts of them here in the forum (upload is availabe from advanced editor)?

Stefan

Not applicable
Author

PO NUMBER  ;STATUS; SOURCE ;         SOURCE NAME          ;CTRY;RECD;ORDER DT;ORIG DUE;DUE DATE;PCKUP DT;ETA DATE;TYPE;LV;CST;STYLE           ;SIZE  ;COLOR ;FG;SUPPLIER ITEM                 ; OPEN QUANTITY   ; PO NUMBR2;MGR;SHIPMENT NR.;CONTAINER   ;CRE DATE;                                       

000102695801; 800  ;58800005;NAIGAI CO. LTD                ;5880;D   ;20110617;20111015;20111015;20111015;20111014; 010;60;972;81131           ;100130;BLACK ;30;81131       1013    BLACK     ; 000000079.000000;0001026958;MM ;000044468031;000044468030;20110909;                                       

000102695801; 800  ;58800005;NAIGAI CO. LTD                ;5880;D   ;20110617;20111015;20111015;20111015;20111014; 010;60;972;84794           ;100130;ASST  ;30;225457/225451/225459/225455   ; 000000026.000000;0001026958;MM ;000044468031;000044468032;20110909;                                      

PO NUMBER  ;STATUS; SOURCE ;         SOURCE NAME          ;CTRY;RECD;ORDER DT;ORIG DUE;DUE DATE;PCKUP DT;EX-V DT ;TYPE;LV;CST;STYLE           ;SIZE  ;COLOR ;FG;SUPPLIER ITEM                 ; OPEN QUANTITY   ;BR; PO NUMBR2;MGR;                           

000000010201; 400  ;57000200;ZHEJIANG FOUR SEASONS HOSIERY ;5700;2   ;20110920;20111227;20111227;20111227;20111204; 010;60;961;64573A-WHITE    ;090110;WHITE ;01;64573A-WHITE     090110 WHITE ; 000000096.000000;KB;0000000102;DMS;                           

000000010201; 400  ;57000200;ZHEJIANG FOUR SEASONS HOSIERY ;5700;2   ;20110920;20111227;20111227;20111227;20111204; 010;60;961;51617A-BLUE     ;090110;BLUE  ;01;51617A-BLUE      090110 BLUE  ; 000000096.000000;KB;0000000102;DMS; 

This should be about two lines worth of data from PO8020.txt first and then two lines from PO8000.txt.  Both include the headers as well.

swuehl
MVP
MVP

Hi gr8scott,

still not sure why the Verbatim is not working, honestly I haven't used it up to now.

As a workaround, you could fill the field with spaces (chr(32)) up to 16 characters in total, like

STYLE&repeat(chr(32),16-len(STYLE)) as STYLE,

CST & STYLE&repeat(chr(32),16-len(STYLE)) & SIZE & LEFT(COLOR,5) as [Customer SKU],

Hope this helps,

Stefan

Not applicable
Author

That helps trememdously Stefan.  Thank you very much.