topic Re: Display same EMPNO values in a single row in QlikView

Display same EMPNO values in a single row

qlikviewwizard — Fri, 20 Nov 2020 18:02:30 GMT

Hi All,

I am unable to show same EMPNO values into one row.

EMPNO2 has two rows in the screen. But I want to show in a single row.

How to achieve this?

Please help.

Thanks in advance.

Re: Display same EMPNO values in a single row

datanibbler — Thu, 11 Jun 2015 09:16:08 GMT

Hi,

you can aggregate by EmpNo, then you will have only one - but keep in mind:

- That is an aggregation, so you need a GROUP BY

- That is (imagine) as if you take little sticks and put them in heaps, so the more criteria (= fields) you look at, the more
heaps you have

=> EmpNo2 has different values in some fields in the two rows, so you must not include those fields in the aggregation, else you will again get two lines.

HTH

Re: Display same EMPNO values in a single row

avinashelite — Thu, 11 Jun 2015 09:25:16 GMT

Hi ,

You can try using the Pivot table , here you will not get two records for the same EMP . That could solve your problem

Re: Display same EMPNO values in a single row

rajni_batra — Thu, 11 Jun 2015 09:30:54 GMT

Hi ,

You need to correct your data first either by Aggreation or other way. Your dept is blank for one of the ID but you have transnational value against the same.

if possible share your data model.

Thanks,

Rajni batra

Re: Display same EMPNO values in a single row

jonathandienst — Thu, 11 Jun 2015 09:40:12 GMT

Is this in a pivot table? The problem is you have some entries on your data without a value in Dept.

It would be best to fix this in the source data or in the load script if that is not possible. I you must fix it in the front end, then you will not be able to use Dept as a dimension. You could use it as an expression (eg Concat(Dept, ',')), but you will get a column for each quarter.

Re: Display same EMPNO values in a single row

Anonymous — Thu, 11 Jun 2015 09:42:43 GMT

Hi,

Try Pivot table that will serve your purpose .It will group records which are same .

Re: Display same EMPNO values in a single row

buzzy996 — Thu, 11 Jun 2015 09:44:12 GMT

u can try with calculated dimension on employee number,hopes tht helps.

Re: Display same EMPNO values in a single row

Digvijay_Singh — Thu, 11 Jun 2015 09:59:32 GMT

It looks like data coming from different source and combined manually. It always better to clean data before we try to apply analytical functions and representation. Please share your data model to clarify things as others also suggested. Thanks.

Re: Display same EMPNO values in a single row

qlikviewwizard — Fri, 12 Jun 2015 02:38:31 GMT

Hi all,

I am using the below script.

Please help me.

DEPT:
LOAD DEPTNO,
     DNAME
FROM
EMPLOYEE.xlsx
(ooxml, embedded labels, table is DEPT);
EMP:
LOAD EMPNO,
     ENAME,
     DEPTNO,
     JOB,
     DATE(HIREDATE) AS HIREDATE,
     DATE(LASTDATE) AS LASTDATE,
     INCHARGE2,
     INCHARGE1
FROM
EMPLOYEE.xlsx
(ooxml, embedded labels, table is EMP);
LEFT JOIN (EMP)
SAL:
LOAD *,QUARTER &'-'&YEAR AS QUARTERYEAR;
LOAD EMPNO,
     YEAR,
     MONTH,
     QUARTER,
     DATE(STARTDATE) AS STARTDATE,
     SAL
FROM
EMPLOYEE.xlsx
(ooxml, embedded labels, table is SAL);

COMM:
LOAD YEAR,
     'Q' & QUARTER AS QUARTER,
     EMPNO,
     COMM
FROM
EMPLOYEE.xlsx
(ooxml, embedded labels, table is COMM);


Temp:
LOAD INCHARGE1 AS EMPNO,  
     SAL AS INCHARGE1SAL,
     QUARTERYEAR
        
RESIDENT EMP


WHERE Len(Trim(INCHARGE1)) > 0
;
Temp:
LOAD INCHARGE2 AS EMPNO,  
     SAL AS INCHARGE2SAL,
     QUARTERYEAR
RESIDENT EMP
WHERE Len(Trim(INCHARGE2)) > 0 or ISNULL(LASTDATE)=-1
;

Re: Display same EMPNO values in a single row

qlikviewwizard — Fri, 12 Jun 2015 02:43:13 GMT

Hi jontydkpi

I am using pivot table.

Re: Display same EMPNO values in a single row

qlikviewwizard — Fri, 12 Jun 2015 02:45:12 GMT

Hi Digvijay

Yes, I am combining Dimension table and measure value tables as shown in datamodel EMP,SAL,COMM tables.

Please help me. Thanks in advance.

Re: Display same EMPNO values in a single row

jagan — Fri, 12 Jun 2015 04:24:33 GMT

Hi,

Try this script to fix the DEPTNO issue

DEPT:

LOAD DEPTNO,

DNAME

FROM

EMPLOYEE.xlsx

(ooxml, embedded labels, table is DEPT);

EMPDEPT:

LOAD DISTINCT

EMPNO,

DEPTNO

FROM

EMPLOYEE.xlsx

(ooxml, embedded labels, table is EMP)

WHERE Len(Trim(DEPTNO)) > 0;

EMP:

LOAD EMPNO,

ENAME,

JOB,

DATE(HIREDATE) AS HIREDATE,

DATE(LASTDATE) AS LASTDATE,

INCHARGE2,

INCHARGE1

FROM

EMPLOYEE.xlsx

(ooxml, embedded labels, table is EMP);

LEFT JOIN

SAL:

LOAD *,QUARTER &'-'&YEAR AS QUARTERYEAR;

LOAD EMPNO,

YEAR,

MONTH,

QUARTER,

DATE(STARTDATE) AS STARTDATE,

SAL

FROM

EMPLOYEE.xlsx

(ooxml, embedded labels, table is SAL);

COMM:

LOAD YEAR,

'Q' & QUARTER AS QUARTER,

EMPNO,

COMM

FROM

EMPLOYEE.xlsx

(ooxml, embedded labels, table is COMM);

Temp:

LOAD INCHARGE1 AS EMPNO,

SAL AS INCHARGE1SAL,

QUARTERYEAR

RESIDENT EMP

WHERE Len(Trim(INCHARGE1)) > 0

;

Temp:

LOAD INCHARGE2 AS EMPNO,

SAL AS INCHARGE2SAL,

QUARTERYEAR

RESIDENT EMP

WHERE Len(Trim(INCHARGE2)) > 0 or ISNULL(LASTDATE)=-1;

Regards,

jagan.

Re: Display same EMPNO values in a single row

jagan — Fri, 12 Jun 2015 04:25:38 GMT

Also I noticed that there are lot of Synthetic keys in your app, it is not a good practice to have Synthetic keys in the Datamodel.

Re: Display same EMPNO values in a single row

qlikviewwizard — Fri, 12 Jun 2015 06:23:30 GMT

Hi jagan,

I tried with your script. But it is not working in original application.

Re: Display same EMPNO values in a single row

jagan — Fri, 12 Jun 2015 08:13:24 GMT

It should work, copy the whole script and then try to reload.

Regards,

jagan.