topic Re: Import data from Excel and update a QVD in QlikView

Import data from Excel and update a QVD

ecabanas — Thu, 05 May 2016 15:42:04 GMT

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

Re: Import data from Excel and update a QVD

Anonymous — Fri, 06 May 2016 07:10:15 GMT

Hi Eduard,

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

QVD:

LOAD

Item_id,

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.

Re: Import data from Excel and update a QVD

Gysbert_Wassenaar — Fri, 06 May 2016 07:10:17 GMT

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

Re: Import data from Excel and update a QVD

Kushal_Chawda — Fri, 06 May 2016 07:32:59 GMT

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

Re: Import data from Excel and update a QVD

ecabanas — Fri, 06 May 2016 07:37:45 GMT

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

Re: Import data from Excel and update a QVD

Gysbert_Wassenaar — Fri, 06 May 2016 07:41:08 GMT

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.

Re: Import data from Excel and update a QVD

jonathandienst — Fri, 06 May 2016 07:42:49 GMT

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.

Re: Import data from Excel and update a QVD

MindaugasBacius — Fri, 06 May 2016 07:43:47 GMT

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

Re: Import data from Excel and update a QVD

didierodayo — Fri, 03 Jun 2016 01:09:04 GMT

Hi Gysbert,

I need similar help below and will appreciate you assistance.

Thanks

Updating QVD with excel