Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Concenate & Preceding load

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);

1 Solution

Accepted Solutions
tresesco
MVP
MVP

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.

View solution in original post

4 Replies
tresesco
MVP
MVP

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.

Anonymous
Not applicable
Author

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

tresesco
MVP
MVP

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

Anonymous
Not applicable
Author

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