topic Concenate & Preceding load in QlikView

Concenate & Preceding load

Anonymous — Thu, 24 Aug 2017 09:23:01 GMT

So i have 2 files. from where i'm getting my sales info.

The QVD file are the historical Data. here's all the closed months data. The xslx file are then containing open orders and the last 90 days sales.

So What i'm trying to achieve are to load the qvd file. and then concenate with all the lines, which aren't allready loaded. Since the xlsx file contains 90 days of sales. The 2 files will overlap.

So how to achieve this? My first approach, were to do a peek on maximum invoice date in the qvd file, and then concenate records higher than max date. Problem here are open orders don't have invoice dates. (without going into details, order date can't be used).

Therefor i needed another approach, if i'm to avoid loading order details to another table(which i would prefer).

The data comes without the ID. So i just want to create the ID in the load, on the variable that creates a unique ID. then do a Where not exists ID. which is what i'm trying below.

The problem are that since Unique ID aren't created before the load. Hence i need a preceding load. But this is not working to well for me? I'm thinking maybe i need to load the xlsx to a temp. table. then load the qvd and concenate with the temp. table. and then drop the temp table? Am i correct? Will this be the best approach. if yes. have is this best done? (could some on show the syntax)

Thanks.

Code so Far

____________________________

Sales:

LOAD InvNumber,

InvoiceDate,

'Invoiced' as OrderStatus,

Company,

OrderNumber,

OrderType,

OrderLineNumber,

OrderDate,

(Company & '-' & OrderType & '-' & OrderNumber & '-' & OrderLineNumber) as OrderLineID

FROM

[Sales.qvd] (qvd);

[Current Sales]:

Concatenate(Sales)

LOAD *,

InvNumber,

If(Len(InvNumber)=0, 'Open', 'Invoiced') as OrderStatus,

InvoiceDate,

InvMonth,

InvYear,

Company,

OrderNumber,

OrderType,

OrderLineNumber,

OrderDate;

Load

(Company & '-' & OrderType & '-' & OrderNumber & '-' & OrderLineNumber) as OrderLineID

FROM

[Orders_Ext std3-9-20 NR.xlsx]

(ooxml, embedded labels, header is 1 lines, table is Sheet1) Where Not Exists(OrderLineID);

Re: Concenate & Preceding load

tresB — Thu, 24 Aug 2017 09:35:40 GMT

Not sure if understood right. Perhaps, you just have to create new fields (for second table) in one load statement and do-away with preceding load, like:

Sales:

LOAD InvNumber,

InvoiceDate,

'Invoiced' as OrderStatus,

Company,

OrderNumber,

OrderType,

OrderLineNumber,

OrderDate,

(Company & '-' & OrderType & '-' & OrderNumber & '-' & OrderLineNumber) as OrderLineID

FROM

[Sales.qvd] (qvd);

[Current Sales]:

Concatenate(Sales)

LOAD *,

InvNumber,

If(Len(InvNumber)=0, 'Open', 'Invoiced') as OrderStatus,

InvoiceDate,

InvMonth,

InvYear,

Company,

OrderNumber,

OrderType,

OrderLineNumber,

OrderDate ,

(Company & '-' & OrderType & '-' & OrderNumber & '-' & OrderLineNumber) as OrderLineID

FROM

[Orders_Ext std3-9-20 NR.xlsx]

(ooxml, embedded labels, header is 1 lines, table is Sheet1) Where Not Exists(OrderLineID, (Company & '-' & OrderType & '-' & OrderNumber & '-' & OrderLineNumber));.

Edit: And yes, noticed the Not Exists later, and modified accordingly.

Re: Concenate & Preceding load

Anonymous — Thu, 24 Aug 2017 09:40:50 GMT

The problem are you can't evaluate on the OrderLineID in the xslx load.. I'm guessing it's because the Where not comes before the actual load/creation of each line. therefor qlickview can see all the field from static data, but not the field you are trying to create in the load.

I'm told field OrderLineID does not exist

Re: Concenate & Preceding load

tresB — Thu, 24 Aug 2017 09:43:00 GMT

Please check my edited updated message. You could pass an expression as second argument to exists().

Re: Concenate & Preceding load

Anonymous — Thu, 24 Aug 2017 10:11:47 GMT

ohh sorry.. i didn't notice that one.. so simple.. I had tried this.. just in the wrong way.. Thank you so much..