topic Re: Chart with double header row in QlikView

Chart with double header row

Mon, 29 Jul 2013 07:05:53 GMT

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

Re: Chart with double header row

sivarajs — Mon, 29 Jul 2013 07:28:45 GMT

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

Mon, 29 Jul 2013 07:37:53 GMT

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

Re: Chart with double header row

sivarajs — Mon, 29 Jul 2013 07:46:40 GMT

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

Mon, 29 Jul 2013 08:07:28 GMT

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

training. training code

mandatory training training code

these 2 tables are joined to each other.

In attachment my expression so far.

John

Re: Chart with double header row

sivarajs — Mon, 29 Jul 2013 09:01:12 GMT

could you please attach a sample?

Re: Chart with double header row

Mon, 29 Jul 2013 09:12:54 GMT

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;

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

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;

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

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