26 Replies Latest reply: Feb 19, 2013 12:45 AM by balu_prg RSS

    HELP!!!!!!!!!!!!!!!!!!

      Im really new to qlikView and I need to design a very complex chart and really need help,

       

      Lets assume I have a table as below in excel file,

       

       

      Person Name

      Division

      Desigination

      Change date

      Person AITEngineer1/1/2013
      Person BITEnginer1/1/2013
      Person AITManager2/2/2013
      Person BITAsst Manager3/3/2013

       

      So I want a chart in qlikview which would tell me how many Person from each division has got promotion? So the Chart should display as In IT Division 2 People have got promotion

        • Re: HELP!!!!!!!!!!!!!!!!!!
          Alessandro Saccone

          Here is an example, hope it helps

          • Re: HELP!!!!!!!!!!!!!!!!!!

            When I open the qvw file which you have sent me I just see a table with a value 0 in it?

            • Re: HELP!!!!!!!!!!!!!!!!!!
              Vishwaranjan Kumar

              hi

               

              a:

              LOAD p_name,

                  count(p_name) as count,

                   //division,

                  // disig,

                   max(date(date))

              FROM

              [D:\SAP\QLIKVIEW-QVW-QVD FILE_NEW_2013\TEST.xlsx]

              (ooxml, embedded labels, table is Sheet1)      Group by p_name;

              • Re: HELP!!!!!!!!!!!!!!!!!!
                sujeet Singh

                Balu it is just as you can add a derived field as level and then you can easily do the ranking.

                • Re: HELP!!!!!!!!!!!!!!!!!!
                  Alessandro Saccone

                  this is the expression:

                  sum

                  (if(aggr(min(level),Division, [Person Name])<aggr(max(level),Division, [Person Name]),1,0))

                   

                  the chart has two dimensions:

                  division and person name

                   

                  the script is:

                  load

                  * Inline

                  [Person Name ,Division ,Desigination ,Change date

                  Person A,IT,Engineer,1/1/2013

                  Person B,IT,Engineer,1/1/2013

                  Person A,IT,Manager,2/2/2013

                  Person B,IT,Asst Manager,3/3/2013

                  ]
                  ;



                  left  Join



                  load * Inline

                  [

                  Desigination, level

                  Engineer,1

                  Asst Manager, 2

                  Manager, 3

                  ]
                  ;



                  However try to load data again in the file I sent to you

                  • Re: HELP!!!!!!!!!!!!!!!!!!
                    Alessandro Saccone

                    I hope you can see the example, if not let me know, I've solved the problem

                     

                    Hope it helps

                      • Re: HELP!!!!!!!!!!!!!!!!!!

                        Im not able to open the example file is it possible to paste the code and also help me on if it is possible to display in a chart?

                          • Re: HELP!!!!!!!!!!!!!!!!!!
                            Alessandro Saccone

                            SET

                            ThousandSep='.';

                            SET DecimalSep=',';

                            SET MoneyThousandSep='.';

                            SET MoneyDecimalSep=',';

                            SET MoneyFormat='€ #.##0,00;-€ #.##0,00';

                            SET TimeFormat='hh:mm:ss';

                            SET DateFormat='DD/MM/YYYY';

                            SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

                            SET MonthNames='gen;feb;mar;apr;mag;giu;lug;ago;set;ott;nov;dic';

                            SET DayNames='lun;mar;mer;gio;ven;sab;dom';





                            MainTable:

                            load * Inline

                            [Person Name ,Division ,Desigination ,Change date

                            Person A,IT,Engineer,1/1/2013

                            Person B,IT,Engineer,1/1/2013

                            Person A,IT,Manager,2/2/2013

                            Person B,IT,Asst Manager,3/3/2013

                            ]
                            ;



                            left  Join



                            load * Inline

                            [

                            Desigination, level

                            Engineer,1

                            Asst Manager, 2

                            Manager, 3

                            ]
                            ;



                            left join



                            LOAD

                            Division,

                            [Person Name],

                            max(level) as max,

                            min(level) as min

                            Resident MainTable

                            Group by Division,[Person Name];



                            left  Join



                            load * Inline

                            [

                            From_Desigination, min

                            Engineer,1

                            Asst Manager, 2

                            Manager, 3

                            ]
                            ;



                            left  Join



                            load * Inline

                            [

                            To_Desigination, max

                            Engineer,1

                            Asst Manager, 2

                            Manager, 3

                            ]
                            ;

                             

                             

                            I've designed a table chart

                            Dimension:

                            Division

                            person name

                            from_designation

                            to_designation

                            change_date

                             

                            Expression:

                                

                            aggr

                            (sum(if(min<max,1,0)),Division,[Person Name])

                             

                            If you need help let me know ...

                             

                              • Re: HELP!!!!!!!!!!!!!!!!!!

                                Thanks for this the only problem I see now is If the Person A Changes from Enginer to Asst manager and then To manager then I would expect to display all the changes but that is not happening. Also can't we display this information in some chart instead of a straight table? Please suggest

                                  • Re: HELP!!!!!!!!!!!!!!!!!!
                                    Alessandro Saccone

                                    I've computed only first and last change, if you want a sort of history things changes ... I'll try to display info but tell me how display data in the row!

                                    • Re: HELP!!!!!!!!!!!!!!!!!!

                                      Yes please help me and see if we can display the info and probably in a chart?

                                        • Re: HELP!!!!!!!!!!!!!!!!!!
                                          Alessandro Saccone

                                          If you want history of changes, try the following code:

                                           

                                          MainTable:

                                           

                                           

                                          NoConcatenate

                                          load * Inline

                                          [Person Name ,Division ,Desigination ,Change date

                                          Person A,IT,Engineer,1/1/2013

                                          Person B,IT,Engineer,1/1/2013

                                          Person A,IT,Manager,2/2/2013

                                          Person B,IT,Asst Manager,3/3/2013

                                          Person B,IT,Manager,1/2/2013

                                          ]
                                          ;



                                          Left Join



                                          levels:

                                          load * Inline

                                          [

                                          Desigination, level

                                          Engineer,1

                                          Asst Manager, 2

                                          Manager, 3

                                          ]
                                          ;

                                          then design a table with dimension

                                          Division

                                          person name

                                          level

                                          designation

                                           

                                          Expression:

                                          1

                                           

                                          and sort by

                                          Division

                                          person name

                                          level

                                           

                                          If you use the following expression (for example in a bar chart) you can count the number of changes

                                          aggr

                                          (distinct count(DISTINCT level),Division, [Person Name])

                                           

                                          Hope it helps

                                           

                                          Alexandros

                                            • Re: HELP!!!!!!!!!!!!!!!!!!

                                              I pasted the script as below in the qlikview and then I reloaded the data and created barchart with Dimension as Division,Designation and Person Name and the expr as

                                              aggr

                                              (distinct count(DISTINCT level),Division, [Person Name])  but I see the Bar chart showing Just Person A and Person B info and it is showing only for engineer but not the way I wanted. Like in the chart I want to know in which Divishion which employee is chnaged from which designation to which.

                                                • Re: HELP!!!!!!!!!!!!!!!!!!
                                                  Alessandro Saccone

                                                  I've done 2 chart, a table and a bar graph, in the table I use a dummy expression (e.g. sum(1)), in the bar chart I use the second expression I gave you.

                                                   

                                                  In the table the information is shown by rows.

                                                    • Re: HELP!!!!!!!!!!!!!!!!!!

                                                      Can you please attach the QVW file which you have designed?

                                                       

                                                      Also can you suggest which is the best way to learn scripts in qlikview can you refer some books?

                                                        • Re: HELP!!!!!!!!!!!!!!!!!!
                                                          Alessandro Saccone

                                                          This is the final solution, you can obviously generalize it but it works ...

                                                           

                                                          SET

                                                          ThousandSep='.';

                                                          SET DecimalSep=',';

                                                          SET MoneyThousandSep='.';

                                                          SET MoneyDecimalSep=',';

                                                          SET MoneyFormat='€ #.##0,00;-€ #.##0,00';

                                                          SET TimeFormat='hh:mm:ss';

                                                          SET DateFormat='DD/MM/YYYY';

                                                          SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

                                                          SET MonthNames='gen;feb;mar;apr;mag;giu;lug;ago;set;ott;nov;dic';

                                                          SET DayNames='lun;mar;mer;gio;ven;sab;dom';



                                                          levels_tmp:

                                                          load * Inline

                                                          [

                                                          Desigination, level

                                                          Engineer,1

                                                          Asst Manager, 2

                                                          Manager, 3

                                                          ]
                                                          ;



                                                          MainTable:

                                                          NoConcatenate

                                                          load * Inline

                                                          [Person Name ,Division ,Desigination ,Change date

                                                          Person A,IT,Engineer,1/1/2013

                                                          Person B,IT,Engineer,1/1/2013

                                                          Person A,IT,Manager,2/2/2013

                                                          Person B,IT,Asst Manager,3/3/2013

                                                          Person B,IT,Manager,1/2/2013

                                                          ]
                                                          ;



                                                          Left Join



                                                          levels:

                                                          load * Inline

                                                          [

                                                          Desigination, level

                                                          Engineer,1

                                                          Asst Manager, 2

                                                          Manager, 3

                                                          ]
                                                          ;



                                                          MT:

                                                          NoConcatenate

                                                          LOAD * Resident MainTable;



                                                          FOR i = 1 to 3

                                                          //     LOAD * Resident MainTable;

                                                          Left Join

                                                          LOAD level,Desigination as des_descr$(i) Resident levels_tmp where level=$(i);

                                                          NEXT



                                                          DROP Table MainTable;

                                                          DROP Table levels_tmp;



                                                          All:

                                                          NoConcatenate

                                                          LOAD

                                                          Division, [Person Name], MaxString(des_descr1) as des_descr1MaxString(des_descr2) as des_descr2, MaxString(des_descr3) as des_descr3

                                                          Resident MT

                                                          Group by Division, [Person Name];



                                                          DROP Table MT;

                                                           

                                                           

                                                          THE TABLE CHART DIMENSIONS ARE:

                                                          Division

                                                          Person Name

                                                          des_descr1

                                                          des_descr2

                                                          des_descr3

                                                           

                                                          Use 1 as Expression (you can even hide the column

                                        • Re: HELP!!!!!!!!!!!!!!!!!!
                                          Alessandro Saccone

                                          Here is the document