25 Replies Latest reply: Nov 18, 2010 9:29 AM by Alihankul RSS

    Data-comparision

    Alihankul

      Hi dear Community,

       

      i have a comparison-data-load-problem. I must load monthly project-data in form of excel-files.Therefor i must compare the field "actual cost" with each file. so for example i have the month July and June. In June the actual cost was about 1000 $ and in July about 1500$ dollar. So when i add loading the new monthly excel-file for July, then the programm should replace the old fieldvalue of actual cost(from June) which was about 1000$ and should set an new fieldvalue with the new value of 1500$.

      Can i anybody help me please by solving my problem?

       

       

        • Data-comparision
          Bhushan Nirgude

          Hi ,

          If I get you correct you are basically looking for insert + update function during loading .

          You can check HELP to know more about it .

          I pasted sample code below for your quick reference.

          Insert and Update. (No Delete)

          Script example:

          QV_Table:

          SQL SELECT PrimaryKey, X, Y FROM DB_TABLE

          WHERE ModificationTime >= #$(LastExecTime)#;

          Concatenate LOAD PrimaryKey, X, Y FROM File.QVD

          WHERE NOT Exists(PrimaryKey);

          STORE QV_Table INTO File.QVD;

           

           

          Regards,

          Bhushan N

            • Data-comparision
              Alihankul

              Hi Bhushan,

              first i want to thank you very much for answering and helping me. There is a little problem that i have by understanding your code cause my datas are only in form of excel and not like a database. so is it possible to match this code for using excel files. see, i have one excel file in june and then i want to update the existent data with the data files from july. so i have a monthly update and replace when i have different values for the actual cost field. i hope you can understand me:)))

              best regards

              dilan(but not like dylan) so i ´m a girl and not a boy;)

                • Data-comparision
                  Bhushan Nirgude

                  Hi Dilan ,

                  It will work with your excel files too. Just need to do some coding .

                  If you can share sample data , i can work on sample code for your reference.

                  Regards,

                  Bhushan N

                    • Data-comparision
                      Alihankul

                      Hello Again,

                      i would send you two excel_files if you want to give me your email adress, cause i try to paste a view of document here, but it is not really coherent:)

                       

                        • Data-comparision
                          Hari Sury

                          Another possible way to look at this problem:

                          Store the field as a Year-to-Date (YTD) value. At the time of presentation, use Set Analysis to subtract current month's YTD value with previous month's YTD value and arrive at the current month's actual value.

                          • Data-comparision
                            Bhushan Nirgude
                            HI
                            • Data-comparision
                              Bhushan Nirgude

                              Hi ,

                              you can forward me your sample files to bnirgude @ gmail . com

                              I will work on them & send you QVW through this forum thread .

                              Cheers,

                              Bhushan N

                                • Data-comparision
                                  Alihankul

                                  Hi bnirgude,

                                  ok i will send you the files on your email. Thank you a lot for helping me!!!

                                  Cheers,

                                  Dilan

                                    • Data-comparision
                                      Bhushan Nirgude

                                      Hi Dilan ,

                                      Please find the attached application and i hope this will help you to understand how to implement .

                                      I am sending you complete folder . I placed arbitatory "project_id".

                                      Let me know if you have any doubts on the same .

                                      Cheers,

                                      Bhushan N

                                        • Data-comparision
                                          Alihankul

                                          Hello bhushan,

                                          thank you very much for your support and for helping me. But i think i´m not allowed to open the file with the ending .qvw cause i use the personel edition and i think i get problems with the licence if i open your file ( as far as i know external files from other user are not allowed to use for a personel user like me). Perhaps you can explain me the rigth way to solve my problem or send me the code you have implement in form of .txt Thank you again and again for helping me.

                                          Cheers,

                                           

                                          Dilan

                                            • Data-comparision
                                              Bhushan Nirgude

                                              Hi Dilan ,

                                              No Probs ...

                                              I am attaching the code IN word format .

                                              Simply copy this code & put it in your QV Doc.

                                              Regards,

                                              Bhushan N

                                               

                                               

                                               

                                               



                                               

                                               

                                               

                                               

                                               

                                               

                                               

                                               

                                               

                                               

                                               

                                               

                                               

                                               

                                               

                                               

                                               

                                               



                                               

                                               

                                               

                                               

                                               

                                               

                                               

                                               

                                               

                                               

                                               

                                               

                                               

                                               

                                               

                                               





                                               

                                               

                                                • Data-comparision
                                                  Alihankul

                                                  oh bhushan,

                                                  you are a hero:) thank you very much..again and again...i will just try it with the code and wil tell you about the result!

                                                  thank you very very much!!!

                                                   

                                                  Regards

                                                  Dilan

                                                    • Data-comparision
                                                      Bhushan Nirgude

                                                      Hi Dilan ,

                                                      You are always welcome

                                                      Regards,

                                                      Bhushan N

                                                        • Data-comparision
                                                          Alihankul

                                                          Again hi to you bhushan,

                                                          first i want to tell you that your code is really perfect:) THANKS!!

                                                          and second is, do you now how to set indicators in qlikview, for watching field values and working as a kind of alarms like a signal light:)

                                                          for example: if a current field value is over 1000$ than drop an exception for warning the user or the field will lighten in RED or something like this. i just try it with the alarm function in qlikview but i haven´t done it very well i think cause it is not working perfectly...

                                                          i hope you understand me:))

                                                            • Data-comparision
                                                              Bhushan Nirgude

                                                              Hi Dilan ,

                                                              Check this one .

                                                              Regards,

                                                              Bhushan N

                                                              • Data-comparision
                                                                Bhushan Nirgude

                                                                Hi Dilan ,

                                                                please check this

                                                                 

                                                                Bhushan N

                                                                  • Data-comparision
                                                                    Alihankul

                                                                    Hi bnirgude,

                                                                    i think you sent the same file twice. they have the both ending with .qvw and i´m still not allowed to open them as a personel enduser:)

                                                                      • Data-comparision
                                                                        Bhushan Nirgude

                                                                        Hi Dilan ,

                                                                        Sorry i missed that you are having personal edition .

                                                                        You can follow below steps.

                                                                         

                                                                        Stpe-1) You can create an expression .

                                                                         

                                                                         

                                                                        Step-2) Chart ---> Properties ---> Expressions --->Display Options --- > Representation --------------------- change it to "Image"



                                                                         

                                                                         

                                                                         

                                                                        Stpe-3) & paste below mention code in Definition .

                                                                         

                                                                        if



                                                                        (

                                                                         

                                                                         



                                                                        (

                                                                         

                                                                        (



                                                                        Sum ([Projectcost (actual)])<1000,'qmem://<bundled>/BuiltIn/smiley3_r.png','qmem://<bundled>/BuiltIn/smiley1_g.png')





                                                                        Cheers ,

                                                                        Bhushan N

                                                                          • Data-comparision
                                                                            Alihankul

                                                                            Dear Bhushan,

                                                                            thank you very much for helping me again and again...i will try this way you suggest, i hope it will function:)

                                                                            Thank you!

                                                                            Cheers,

                                                                            Dilan

                                                                            • Data-comparision
                                                                              Alihankul

                                                                              Hi Bhushan,

                                                                              again it´s me:)

                                                                              So 1) where must i insert my expression that i´m creating ( by the "script" option?)

                                                                              2) after i have create an expression like for example (if projectcost actual- projectcost plan<1000) where must i insert the chart? is the chart independent of the expression or are they coherent?

                                                                              3) i´m not sure with the linking up between the expression i create and the chart i create

                                                                              perhaps you can help me on?

                                                                              cheers

                                                                              Dilan

                                                                                • Data-comparision
                                                                                  Bhushan Nirgude

                                                                                  Hi Dilan ,

                                                                                  check this one .

                                                                                  Regards,

                                                                                  Bhushan N

                                                                                    • Data-comparision
                                                                                      Alihankul

                                                                                       

                                                                                      hi again:)

                                                                                      I try this way:

                                                                                       

                                                                                      1. after the loading order (by scripting) i set a order like : let a=projectcost_actual - projectcost_plan ( to get the difference between these both values)

                                                                                      2. then i set the properties for the expressions, for the chart like this:

                                                                                        a) Label "Difference" express the field "a", so i enter this " sum($a)" ( i write this by the "Definition of the Label"

                                                                                        b) Label "Tag" expresses the Red, yellow or green status by this definition ( and i save this as a image like you show me)

                                                                                        if (a <= 1000, 'qmem://<bundled>/BuiltIn/exclamation_r.png')

                                                                                      xor

                                                                                      if (a >1000,'qmem://<bundled>/BuiltIn/exclamation_y.png')

                                                                                      so i have now the problem, that the difference between the field values doesn´t apperar in the chart and that the Tag doesn´t appear in the right color

                                                                                       

                                                                                       

                                                                                      have you got a idea to solve this problems?

                                                                                      Thank you very much for helping:)

                                                                                      cheers

                                                                                      dilan

                                                    • Data-comparision
                                                      s j

                                                      Following scenarion insert and update and delete in existing QVD.

                                                      Load * From NewData;

                                                      concatenate

                                                      Load * from Historicaldata

                                                      where not exist (PKey);