Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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.
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?
You may test the existance of the file with
IF NOOFROWS ('POS_Master_final') THEN
....; // table exists
ELSE
....; // table does not exist
END IF
hey
where to write this code?
after loading which file?
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.
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 ?
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.
so according to you table declared in the brackets should be present in script with exactly same columns mentioned?
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.