6 Replies Latest reply: Jul 29, 2013 5:12 AM by John Ophof RSS

    Chart with double header row

    John Ophof

      Hi,

       

      Maybe an easy question. I have a chart which I changed a bit (extra column). After the change I got 2 header rows. I don't know where the 2nd one is coming from. I checked all tabs.ScreenShot234.jpg

       

      Maybe a tougher question. Another strange thing if I add the training code after the first column (training name) some columns have missing entries (=blank). If I create a table then every training name has a training code as aspected.

       

      John

        • Re: Chart with double header row
          sivaraj seeman

          Its because you are using expression total in total mode.Just uncheck by chart properties->Expression tab->select No total under Total Mode

            • Re: Chart with double header row
              John Ophof

              This helps a lot thanks for your fast reply. The issue with the empty entries in the column training code (2nd column) still holds. See screen shot accompanied with a training debug table.

               

              JohnScreenShot237.jpg

                • Re: Chart with double header row
                  sivaraj seeman

                  since the data is there for the column, you need to check your logic in expression what you have used.

                    • Re: Chart with double header row
                      John Ophof

                      Thanks. Where can I find the logic. I just added my column data I can do it from

                       

                      training. training code

                      or

                      mandatory training training code

                       

                      these 2 tables are joined to each other.

                       

                      In attachment my expression so far.ScreenShot238.jpg

                       

                      John

                        • Re: Chart with double header row
                          sivaraj seeman

                          could you please attach a sample?

                            • Re: Chart with double header row
                              John Ophof

                              Sample picture? Or a qview file. It has a lot of data with privacy issues attached to it.

                              What do you want to see? Some examples of the sources files? The load script?

                               

                              Every data changing training data

                               

                              Let vDir = '$(vPathCL)' & '*.xlsx';

                              FOR Each file in FileList(vDir)

                                     ODBC CONNECT32 TO [Excel Files;DBQ=$(file)];

                                     tables:

                                     SQLtables;

                                     DISCONNECT;

                              NEXT

                               

                              FOR i = 0 to NoOfRows('tables')-1

                                     LET sheetName = purgeChar(purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39)), chr(36));

                                     LET file_path = purgeChar(purgeChar(peek('TABLE_CAT', i, 'tables'), chr(39)), chr(36));

                                     IF sheetName='Sheet1' THEN

                                     Career_Learning:               

                                     LOAD distinct

                                           [Activity Code] AS TRAINING_CODE,

                                           [Activity Name] AS TRAINING_NAME,

                                           [LocalEmpNumber] AS EMPLOYEE_ID,

                                           [Registration Status] AS TRAINING_REGISTRATION_STATUS,

                                           Year([Attempt Completion Date]) AS TRAINING_COMPLETION_YEAR,

                                           Month([Attempt Completion Date]) AS TRAINING_COMPLETION_MONTH,

                                           MonthStart([Attempt Completion Date]) AS TRAINING_COMPLETION_YYYYMM    

                                     FROM $(file_path) (ooxml, embedded labels, table is [$(sheetName)]) where IsNum([LocalEmpNumber]) and [Registration Status] = 'Completed';

                                     ENDIF

                              NEXT

                              DROP Table tables;

                               

                              This is the load for the (every month not changing) list of mandatory codes. A few records in it.

                               

                              Let vDir = '$(vPathMT)' & '*.xlsx';

                              FOR Each file in FileList(vDir)

                                     ODBC CONNECT32 TO [Excel Files;DBQ=$(file)];

                                    tables:

                                     SQLtables;

                                     DISCONNECT;

                              NEXT

                               

                              FOR i = 0 to NoOfRows('tables')-1

                                     LET sheetName = purgeChar(purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39)), chr(36));

                                     LET file_path = purgeChar(purgeChar(peek('TABLE_CAT', i, 'tables'), chr(39)), chr(36));

                                     IF sheetName='Sheet1' THEN

                                           MandatoryTraining:               

                                           LOAD

                                           [Activity Code] AS TRAINING_CODE, //KEY for Training table

                                           [Activity Code] AS MAND_TRAINING_CODE,

                                           [Activity Name] AS MAND_TRAINING_NAME,

                                           [Mand. Group] AS MAND_TRAINING_GROUP,

                                           [Expires] AS MAND_TRAINING_EXPIRES,

                                           1 AS MAND_TRAINING_IND_DONE

                                     FROM $(file_path) (ooxml, embedded labels, table is [$(sheetName)])

                                         where not WildMatch('Activity Code',' ') or len('Activity Code') <> 0;

                              // changed 28-3

                                     ENDIF

                              NEXT

                               

                              The join

                               

                              MAN_TRAININGS:

                              LOAD Distinct

                                  MAND_TRAINING_NAME,

                                  MAND_TRAINING_CODE AS TRAINING_CODE,

                                  MAND_TRAINING_GROUP

                              RESIDENT MandatoryTraining;

                               

                              LEFT JOIN (MAN_TRAININGS)

                               

                                  LOAD

                                      EMPLOYEE_ID

                                  RESIDENT Career_Learning;

                               

                              John