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: 
ecabanas
Creator II
Creator II

Import data from Excel and update a QVD

Hi all,

I have a QVD with the products prices and I want to update the new product prices.

My idea is load from an Excel, créate a table and this table update the main Price QVD

Is it posible?

Many thank's

Eduard

1 Solution

Accepted Solutions
Gysbert_Wassenaar

Use the Table Files wizard in the script editor to select your excel file. You can't use a file manager.

Load the data from the excel file and append the data from the qvd file to it. Then store the complete table into the qvd. Or the other way around: load the data from the qvd file and append the excel data to it and then store the table to qvd again.


talk is cheap, supply exceeds demand

View solution in original post

8 Replies
Anonymous
Not applicable

Hi Eduard,

You can load QVD file, then load Excel File with a left join, like this:

QVD:

LOAD

Item_id,

a,

b,

price

FROM QVD.qvd (qvd);

//it is better to do a copy

STORE QVD into QVD-copy.qvd (qvd);

Excel:

Left Join (QVD)

LOAD

Item_id,

a               as aNew,

b               as bNew,

price           as priceNew

FROM ExcelFile;

NoConcatenate

LastTable:

LOAD

Item_id,

IF(IsNull(aNew),a,aNew)     as a,

IF(IsNull(bNew),b,bNew)     as b,

IF(IsNull(priceNew),price,priceNew) as price

RESIDENT QVD;

STORE QVD into QVD.qvd (qvd);

Kind regards.

Gysbert_Wassenaar

Yes. Load the data from excel and store the resulting table in the qvd.


talk is cheap, supply exceeds demand
Kushal_Chawda

If you just want to replace the Product and price value then you can just load the data from excel and store it into the QVD

ecabanas
Creator II
Creator II
Author

Hi Gysbert,

1º Is it possible to open a file manager to select where the file is?

2º I want to add data to the QVD, not replace it,

Many many thank's

Eduard

Gysbert_Wassenaar

Use the Table Files wizard in the script editor to select your excel file. You can't use a file manager.

Load the data from the excel file and append the data from the qvd file to it. Then store the complete table into the qvd. Or the other way around: load the data from the qvd file and append the excel data to it and then store the table to qvd again.


talk is cheap, supply exceeds demand
jonathandienst
Partner - Champion III
Partner - Champion III

Something like this:

T_Products:

LOAD ProductKey,

  Price as T_Price,

  ...

FROM Products.qvd (qvd);

Left Join (T_Products)

LOAD Productkey,

  Price as NewPrice,

  1 as UpdateFlag

FROM PriceUpdate.xlsx

(ooxml, ....);

Products:

LOAD *,

  If(UpdateFlag = 1, NewPrice, T_Price) as Price

Resident T_Products;

Store Products into Products.qvd (qvd);

This should get you started. You will need to amend the above to the correct field names and file paths; and fill in the blanks.

You may also want to drop the UpdateFlag and T_Price fields (Drop Fields UpdateFlag, T_Price;) before storing the result.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
MindaugasBacius
Partner - Specialist III
Partner - Specialist III

Take a look at the example attached.

You can apply it to your business needs.

Data:

LOAD Product,

     Price,

     Comment

FROM

(qvd);

Left Join (Data)

LOAD Product,

     Price as [New price]

FROM

(ooxml, embedded labels, table is Sheet1);

UPDATE: after all the sintax you can store everything back to qvd:

STORE Data into ;

or just rewrite onto the old one.  C:\Users\Mindaugasb\Desktop\Data.qvd

didierodayo
Partner - Creator III
Partner - Creator III

Hi Gysbert,

I need similar help below and will appreciate you assistance.

Thanks

Updating QVD with excel