topic Re: how to insert record from UI in QlikView

how to insert record from UI

Wed, 25 Nov 2020 16:16:04 GMT

I'd like to build a what if tool, I know we can use INPUTFIELD to modify existing record values with table box, but what if I want to add new record instead of adjusting existing one, what is in my mind is that we can place an input box for each column, user can manually input the value and click "Add" and insert this new record in existing table, how can I achive this? Thanks.

Re: how to insert record from UI

ahaahaaha — Tue, 04 Jul 2017 08:47:52 GMT

Hi,

As a record in the data warehouse of QlikView you can not insert from UI. Only by downloading the script.

Regards,

Andrey

Re: how to insert record from UI

RonaldDoes — Tue, 04 Jul 2017 08:52:52 GMT

Strictly speaking, you could:

have the user input the new value into an input box
have the "add" button (partially) reload the document
load the variable (set by the input box) into your table as a new record

Fruit:
LOAD
     Fruit,
     Color
FROM
fruit.qvd
(qvd);

Concatenate(Fruit)
LOAD * INLINE [
    Fruit, Color
    $(vFruit), $(vColor)
];

Store Fruit into fruit.qvd;

I've attached my demo. I hope it helps you.

With kind regards,

Ronald

Re: how to insert record from UI

effinty2112 — Tue, 04 Jul 2017 09:06:42 GMT

Hi Qiang,

As Ronald says you CAN add a record to your data model but as Andrey says you CANNOT add a record to the source database.

So when you talk about adding a new record, to what do you want to add the record to?

Regards

Andrew

Re: how to insert record from UI

passionate — Tue, 04 Jul 2017 09:12:40 GMT

Hi,

You can use Dynamic Update option in Qlikview to insert records from UI.

Refer link https://community.qlikview.com/thread/45050

Regards,

Pankaj

Re: how to insert record from UI

jmvilaplanap — Tue, 04 Jul 2017 09:13:50 GMT

Hi effinty2112

You can add records to a datasource using a SQL Statement, INSERT or UPDATE. But I don´t know if this is what qiangs_2017‌ want to do.

Re: how to insert record from UI

effinty2112 — Tue, 04 Jul 2017 09:34:33 GMT

Hi Jose,

You're correct of course and I hesitated before saying "CANNOT" but going down that route is not for the fainthearted and takes us away from standard QlikView functionality. Although QV can run macro scripts I consider that stepping outside of QV. You may disagree. If you write a script that successfully executes SQL commands on the source database using variables from the QV app without the user leaving QV then you have justification in taking a different point of view.

I have a couple of apps that do update flags on a SQL Server database but adding new records is something I would never do from within QV, but that's just my view and I can't say that you're wrong.

Kind regards

Andrew

Re: how to insert record from UI

jmvilaplanap — Tue, 04 Jul 2017 09:45:01 GMT

Of Course effinty2112, Qlikview is not a tool to modify SQL databases, there are a lot of tools that do it better. I never INSERT in a database from QV (only UPDATE flags as you).

I don't know what our colleague exactly need, maybe he needs to write in a table when the Qlik app is connected to the database as a log, then he can do it with Qlik.

As all the best practices rules and programming patters, you can not to follow step by step if is necessary in your development.

Regards

Re: how to insert record from UI

Tue, 04 Jul 2017 09:46:50 GMT

hi Ronald,

this is the exact case I need to deal with, this is my first idea in my mind to append the data into QVD and reload, but it is a heavy operation if the volume is big, is there any smarter way we can try?

btw your demo doesn't work for me, I tried to add some fruit but the table remained the same.

Thanks

-Stan

Re: how to insert record from UI

Tue, 04 Jul 2017 09:52:20 GMT

I agree, modifying database within QV is not good idea,I prefer changing inside of QV(memory) for "what if" purpose, I don't need to store the changes back to original source.

Re: how to insert record from UI

Tue, 04 Jul 2017 09:58:38 GMT

I don't need to insert the change back into database, add to qv data model is enough.

Re: how to insert record from UI

jmvilaplanap — Tue, 04 Jul 2017 10:31:12 GMT

Then, I think that the ronalddoes solution is the only way.

You can use partial reload to do it lighter (What is meant by partial reload? What is its use? Please explain!) but, you must to reload the script.

Regards

Re: how to insert record from UI

Tue, 04 Jul 2017 10:35:10 GMT

hi Pankaj,

I think the Dynamic update works, I will try something with this approach.

Thanks a lot

-Stan

Re: how to insert record from UI

RonaldDoes — Tue, 04 Jul 2017 10:47:56 GMT

Hi Stan,

I'm sorry, I should have shared the fruit.qvd as well.

Here you go. Save both in the same location and you're good to go.

Re: how to insert record from UI

RonaldDoes — Wed, 05 Jul 2017 07:25:24 GMT

Hi Jose,

A partial reload would be a great way to add the "new fruit" to the data model of the QVW, however it will never store it into QVD, since only the ADD LOADs are executed.

This means that after a full reload, your additions would be gone again.

That's why I suggested a full reload.

Re: how to insert record from UI

jmvilaplanap — Wed, 05 Jul 2017 08:50:39 GMT

Hi Ronald

Can't you store a QVD with the new data?

I think if you can reload it then you can store it, isn't it?

Regards

Re: how to insert record from UI

RonaldDoes — Wed, 05 Jul 2017 11:24:14 GMT

Hi Jose,

You are right! I assumed the QVD wouldn't be stored during a partial reload, but testing it appears to be working now.

I wonder what else is executed during partial reloads apart from the ADD LOADs.