topic Re: When Verbatim=1; doesn't work. in QlikView

When Verbatim=1; doesn't work.

Mon, 10 Oct 2011 20:06:51 GMT

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

When Verbatim=1; doesn't work.

swuehl — Mon, 10 Oct 2011 20:18:39 GMT

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

Re: When Verbatim=1; doesn't work.

Mon, 10 Oct 2011 20:54:50 GMT

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.

When Verbatim=1; doesn't work.

swuehl — Mon, 10 Oct 2011 21:31:11 GMT

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

Re: When Verbatim=1; doesn't work.

Tue, 11 Oct 2011 12:57:04 GMT

That helps trememdously Stefan. Thank you very much.