Chart with double header row

[#1]

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.

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

[sivarajs]

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

[#3]

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.

John

[sivarajs]

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

[#5]

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.

John

[sivarajs]

could you please attach a sample?

[#7]

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