Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

chinnakatikisg
Valued Contributor

Is Qlikview can read value from a excel for calulations?

Dear Gurus,

I have requirement to read values from a excel file(value in excel is a Flag value) into a variable to use in my calculations(without reload).

Here is my Orginal requirement:

In a TV Monitor we are showing daily sales progress chart..That chart has to switch different products like: TotalSales==>ProductA==>ProductB==>Product3==>TotalSales ..in this order cycle for every 1 minute automatically.

For this requirement I have used now function to switch the products .. However, this now() is not working to rotate automatically in access point??(unless we keep the session interactive or using the auto refresh web page plug-in)..Also now() is consuming 100% CPU..                       

Now I have thinking of alternatives..

To change value in my variable for every 1 minute..so that I use that value in chart..Is this possible to change value using Macro? (Pls share some sample macro that can change value of my variable every 1 minute)

if not , is it possible to read a value from excel file into variable ?(so that I will change the value in excel file using some external program )

Please share your thoughts. Thank you so much in advance.

Br,

Chinna

2 Replies
chinnakatikisg
Valued Contributor

Re: Is Qlikview can read value from a excel for calulations?

Hi , To read value from excel for calculations we can use attached QVW with direct discovery concept by creating ODBC to excel sheet.

Also to switch the sheet certain time intervals we can use Stefan Walther's SheetJump extension.

We have modified this extension to generate a sequence number and store in a variable for every defined time interval.

And making the number sequence to loop between 1 to 24(as I have to make 24 products to show as slide show in my chart).

Attached the original and modified extension documents as it may be useful for some one.

Below link will guide how to add extensions in Qlikview desktop and server.

http://www.qlikblog.at/1597/qliktip-40-installingdeploying-qlikview-extensions/

Thanks to #stefanwalther for his extension. it saved our TV Dashboard.

Br,

Chinna

ahaahaaha
Honored Contributor

Re: Is Qlikview can read value from a excel for calulations?

Hi Chinna,

Auto reload Using Variable (look attached file).

1) Create variable vTriggerReload with value

=if((Now()-ReloadTime())*1440 >= 1, vTriggerReload+1, 0)


Red of 1 - interval reload by minutes.

2) Create VB script macros

Sub ReloadDoc

   ActiveDocument.Reload

   ActiveDocument.Save

End sub

3) In the document properties on the "Triggers" tabs in the event triggers of the variables in the "When changing" field, specify the name VB macros.

All is ready, the document will be reloaded every minute. Perhaps the first download will have to be done manually.

Regards,

Andrey