how to reload the data from mysql for every ten minutes...

[#1]

hiii

     My scenario goes something like dis...

I have to extract data from mysql for every ten minutes and get into qlikview ,so my question is whether i can create a qvd or directly take data from mysql if i create qvd den how can reload tat qvd automatically once the reload is done from mysql and if i take data directly from mysql (I m nt sure whether dis is the correct method) where and how the transformations should be done.

After a search in communities i gt tat v can create a .bat file and schedule the job with windows scheduler

               This is my understanding plz help me out in this issue if any other approach to solve this welcome

[#2]

Hi Swathi,

i got some code here..hope you can modify accordingly..

qvd_control:   (Loading the data into temp table)

            LOAD qvd_file_name,

            qvd_path,qvd_table_name,qvd_recreate_flag,qvd_reload_back_days,qvd_reload_forward_days;

•         SQL

•         SELECT qvd_file_name, qvd_path, qvd_table_name, (e.g. Tblperformance)

•         qvd_recreate_flag, qvd_reload_back_days, q vd_reload_forward_days

•         FROM  [dbo].[qlikview_qvd_control]

•         WHERE            qvd_file_name = '$(qvd_file_name)';

•         ($(qvd_file_name) will evaluate the value in qvd_file_name variable)

LET qvd_path              =          peek('qvd_path');

            LET qvd_table_name              =          peek('qvd_table_name');

            LET qvd_recreate_flag            =          peek('qvd_recreate_flag');

            LET qvd_max_day_key            =          peek('qvd_reload_back_days');

            LET qvd_save_to                     =          '$(qvd_path)' & '\' & '$(qvd_table_name)' & '.qvd';

            (Note: LET command evaluates the values; peek command is used to get the values from the qlikview variables that are loaded in the qvd_control table)

            Load (Tab)

            $(qvd_table_name): (Here the QVD name is evaluated as performance)

            LOAD

            DayKey&'-'&AgentId as DA,

            vc_ACDCalls as F_ACDCalls,

            DayKey;

            SQL

            SELECT  int_Day_Key AS DayKey,

            bint_Minacs_Emp_ID AS AgentId,

            vc_ACDCalls

            FROM test.tblperformance WHERE int_Day_Key > $(qvd_max_day_key);

•         (NOTE that variable qvd_max_day_key was already populated in the PREPARE block.)

MaxDayKey:

            LOAD MAX(DayKey) as MaxDayKey

            RESIDENT $(qvd_table_name);

            LET MaxDayKey = peek ('MaxDayKey');

             (In the above script we are loading the max(daykey) value in the MaxDayKey temp table. The source the newly loaded temp qvd table $(qvd_table_name))

            Merge (TAB)

            The newly extracted data now has to be concatenated with the old historical data.

            We are going to add the historical data to the $(qvd_table_name) (in our case tblperformance)

            IF '$(qvd_recreate_flag)' = 'N' THEN

            Concatenate ($(qvd_table_name))

            LOAD

            DA,

            DayKey

            FROM  $(qvd_save_to) (qvd)

            WHERE            DayKey <= $(qvd_max_day_key);

            END IF

Store (TAB)

            The final step is to store the data in the temp table $(qvd_table_name) (our case performance) to a qvd file.

            IF ScriptErrorCount = 0 THEN

                        STORE $(qvd_table_name) INTO $(qvd_save_to);

                        (STORE Command to stored in the qvd file)

                        IF IsNull(MaxDayKey)=0 THEN

                                    SQL

                                    UPDATE          [dbo].qlikview_qvd_control

                                    SET      qvd_recreate_flag = 'N',

                                    qvd_reload_back_days = $(MaxDayKey)

                                    WHERE            qvd_file_name = '$(qvd_file_name)';

                        END IF

                        (Above update statement will populate the new dayKey for next day’s processing indicating the last daykey that we have already processed)

                        DROP TABLE $(qvd_table_name);

                        (Dropping the TempTable (our case performance) as it is not required anymore)

            END IF

Regards,

Chandra

[#3]

Hii Chandra,

                  Thanks for your help..

                  To be frank i didnot understand the code can u plz explain wat exactly

                    is happening in the above code...??

[#4]

Hi Swathi,

This is incremental load script !!

First it will take the last reload time of the QVD and will extract new data from Source and storing into Temp table. after that merging temp table with historical QVD.

Regarding window schedulor i have no idea..

Regards,

Chandra