Why data is duplicated?

vchuprina — Tue, 28 Jul 2015 21:18:50 GMT

Hi guys,

Can you please help.

I have two types of files. In one file I have 3 sheets and data from second and third sheets is duplicated. Data for this file should load by second tab, but it duplicated when I have file that loaded by first tab.

Also i tried to connect to ODBC and use IF else, but had the same result

How can I resolve this issue.

First tab

FOR each vFile in Filelist ('$(vDataFolder)\*.xls')

// connect to eaach Excel file

// sqltables seems to add a random $ sign and single quotes

ODBC CONNECT32 TO [Excel Files;DBQ=$(vFile)];

// Read list of sheets

Temp_Tables:

sqltables;

DISCONNECT;

// Get just the file name

let vFileName = mid(vFile, index(vFile, '\', -1) + 1);

// Enumerate sheets

for iSheet = 0 to NoOfRows('Temp_Tables') - 1

let vSheetName = peek('TABLE_NAME', iSheet, 'Temp_Tables');

let vSheetName = replace(replace(replace(vSheetName, chr(39), ''), chr(36),''),'#','.'); // sqltables seems to add a random $ sign and single quotes

IF ((WildMatch(upper(vSheetName),'*PREFALL*','PRE-FALL', 'BA', 'FALL')) AND NOT WildMatch((vSheetName),'*Print*','*Database*', '*Detail*')) THEN

//*******************************

// Get Header row number

//******************************

T1:

LOAD @1,

rowno() AS rownumber

FROM

[$(vFile)]

(biff, no labels, table is [$(vSheetName)$]);

NoConcatenate

T2:

LOAD * , 1 AS one

Resident T1

WHERE WildMatch(@1,'*VNDR*')

;

Drop Table T1;

Let vRowNum = peek('rownumber',0,'T2')-1;

Drop Table T2;

//******************************

// Get Detail Data

//******************************

Data:

LOAD

FROM

[$(vFile)]

(biff, embedded labels, header is $(vRowNum) lines, table is [$(vSheetName)$])

WHERE NOT ISNULL([VNDR STYLE] ) AND TRIM([VNDR STYLE]) <> '' AND [VNDR STYLE] <> 'TOTAL' AND NOT ISNULL([COLOR]) AND TRIM([COLOR]) <> ''

;

END IF

DROP TABLE Temp_Tables;

FOR each vFile in Filelist ('$(vDataFolder)\*.xls')

// connect to eaach Excel file

// sqltables seems to add a random $ sign and single quotes

Second tab

ODBC CONNECT32 TO [Excel Files;DBQ=$(vFile)];

// Read list of sheets

Temp_Tables:

sqltables;

DISCONNECT;

// Get just the file name

let vFileName = mid(vFile, index(vFile, '\', -1) + 1);

// Enumerate sheets

for iSheet = 0 to NoOfRows('Temp_Tables') - 1

let vSheetName = peek('TABLE_NAME', iSheet, 'Temp_Tables');

let vSheetName = replace(replace(replace(vSheetName, chr(39), ''), chr(36),''),'#','.'); // sqltables seems to add a random $ sign and single quotes

IF( (WildMatch(upper(vSheetName),'*Detail*')) AND NOT WildMatch((vSheetName),'*Print*','*Database*','*Summary*')) THEN

//*********************************

// Get Header Row To Start At

//*********************************

Temp:

LOAD @1,

@2,

@3,

@9,

RowNo() AS RowNumber

FROM

[$(vFile)]

(biff, no labels, table is [$(vSheetName)$]);

T2:

NoConcatenate

LOAD * Resident Temp

WHERE WildMatch(@2,'*Vendor Style*')

;

DROP Table Temp;

LET vRowStart = peek('RowNumber',0,'T2');

DROP TABLE T2;

//************************************

// Get the totals

//************************************

Data:

LOAD Distinct

FROM

[$(vFile)]

(biff, no labels, Header is $(vRowStart), table is [$(vSheetName)$])

Where @1 <> 'GRAND TOTAL' AND ISText(@9) AND @10 <> 'Cls';

END IF

DROP TABLE Temp_Tables;

Store data into ...

Re: Why data is duplicated?

marcus_sommer — Wed, 29 Jul 2015 11:42:22 GMT

It looked that you are loading the data twice. I suggest that you adds filebasename(), $(vSheetName), recno + rowno and a loop-counter to your loads and then you could see from where which data comes and if you expected it this way.

- Marcus

topic Re: Why data is duplicated? in QlikView

Why data is duplicated?

Re: Why data is duplicated?