which fie to concatenate in incremental load ?

[Anonymous]

Hi

I want to concatenate file(POS_master_final) in incremental load so i want to know is this file already available in or i have to create this ?file before concatenating?

[tresesco]

If you write Concatenate(T1) in the script, while executing the engine would expect T1 table to be previously loaded and present, if not found, it would through an error.

[Anonymous]

Hi

I want to confirm whether POS_master file is the file which is to be concatenated in the incremental load . please see the script:

//POS_Master:
//Load
//ETPCode,
//[inv no],
//Sold_Date,
//Sold_Month,
//Sold_Year,
//itemnumber,
//[Season Code],
//quantity_sold,
//[sales price],
//[inv value],
//[tax amount],
//[Inv val Bucket],
//[return no],
//quantity_returns
////quantity_received,
////First_Received_Date
//FROM
//D:\Data\Final QVD\Final\POS_Master_incremental.qvd (qvd)
//where Sold_Date <= MakeDate(2015,02,28) and NOT WildMatch(ETPCode,'*ETP*','N*N','N*');

and 

Incremental

  OLEDB

CONNECT

TO

[Provider=SQLOLEDB.1;Persist Security Info=True;User ID=sa;Initial Catalog=ETPEAS;Data Source=192.168.1.70;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=QLIKVIEW;Use Encryption for Data=False;Tag with column collation when possible=False](

XPassword

is XEDJTZFMLB);

Increment_Sales:

Concatenate(POS_Master_final)

Load

Warehouse

as

ETPCode,

InvoiceNumber

as

[inv no],

Date#(

CreateDate, 'YYYYMMDD')

as

Sold_Date,

month(

Date#(

CreateDate, 'YYYYMMDD'))

as

Sold_Month,

year(

Date#(

CreateDate, 'YYYYMMDD'))

as

Sold_Year,

replace(

ItemNumber,' ','')

as

itemnumber,

left(

ItemNumber,3)

as

[Season Code],

InvoiceQuantity

as

quantity_sold,

SalesPrice

as

[sales price],

LocalAmount

as

[inv value],

if (

LocalAmount <500, '<500',

if (

LocalAmount <800, '500-799',

if (

LocalAmount <1200, '800-1199',

if (

LocalAmount <1600,'1200-1599',

if (

LocalAmount <2500,'1600-2499',

if (

LocalAmount <3500,'2500-3499',

if (

LocalAmount <5000,'3500-4999','>=5000')))))))

as

[Inv val Bucket],

VatAmount

as

[tax amount];

SQL

SELECT

*

FROM

ETPEAS.dbo.CashOrderTrn

WHERE

InvoiceType

=

'31'

and

(CreateDate

>=

20130101

and

CreateDate

<=

'$(currentdate)') ;

so in the above script POS_Master and POS_Master_final(incremental)(marked in bold) file is same?

[prieper]

You may test the existance of the file with

IF NOOFROWS ('POS_Master_final') THEN

....;          // table exists

ELSE

....;          // table does not exist

END IF

[Anonymous]

hey

where to write this code?

after loading which file?

[jonathandienst]

Be aware that NoOfRows() can return 0 if the tables exists but has no rows, so it is not bulletproof.

Try this script to prove it yourself:

Data:

LOAD 0 as A AutoGenerate 0;

vTest = NoOfRows('Data');

Rather use this logic:

If Alt(NoOfRows('POS_Master_final') , -1) >= 0 Then...

NoOfRows() will return a null (which Alt() converts to -1) if the table does not exist. This is bulletproof.

[Anonymous]

i want to just confirm which file we actually concatenate  in incremental load?

according to my script POS_MASTER and POS_master_final file means the same file ?

[prieper]

Within your script, it is not clear, whether is it complete.

As per the sniplet supplied, the

Concatenate(POS_Master_final)

will run into an error, as there is no table POS_Master_final (visible).

If there is, then all fields from the new table will be concatenated to the table declared in (brackets).

Be careful: QV also concatenates all tables, which share the same fields, under the name of the first table.

Use of NOCONCATENATE LOAD .... may prevent this behaviour.

[Anonymous]

so according to you table declared in the brackets should be present in script with exactly same columns mentioned?

[prieper]

Depending,

if you have

TableA: LOAD A, B FROM ....;

TableB: LOAD A, B FROM ....;

Then there will be an automatic concatenation of the data from TableB to TableA.

If you would have

TableC: LOAD A, B, C FROM ....;

will not be concatenated, but QV will try to link Fields A and B btw TableA and TableC.

Other case might be:

TableA: LOAD A, B FROM ....;

TableB: CONCATENATE (TableA) LOAD A, B, C

This will force all fields from TableB to be concatenated to TableA

You have provided only part of your script. This sniplet does not show the creation of a table named POS_Master_final. If this was created before, the script should work. If not, it will run into error.