topic Re: Values in a Table become a Column in QlikView

Values in a Table become a Column

kfahri342 — Tue, 13 Mar 2018 18:03:12 GMT

Hi Qlikers,

I have the following underlying data table in excel

Name	Exam 1	Exam 2	Exam 3	Exam4
Steve	Jun-18	Jun-18	Nov-18	Unknown
Louise	Feb-18	Jun-18
Michael	Unknown	Unknown	Unknown
Emma	Dec-18	Jan-18	Unknown
Joe	Aug-18			Feb-18

I need to transpose this into Qlikview and create the table below. [i.e. the date and text values in the above table become columns, and the values of the below are a count of the Names]

Exam Type	Jan-18	Feb-18	Mar-18	Apr-18	May-18	Jun-18	Jul-18	Aug-18	Sep-18	Oct-18	Nov-18	Dec-18	Unknown	Grand Total
Exam 1		1				1		1				1	1	5
Exam 2	1					2							1	4
Exam 3											1		2	3
Exam 4		1											1	2
Grand Total	1	2	0	0	0	3	0	1	0	0	1	1	5	14

Is this possible, I have read some other forums and it seems Cross Tables may be the solution, but have gone through them I still can't make it work. Is any able to demonstrate this please.

Many Thanks

Kozan

Re: Values in a Table become a Column

Digvijay_Singh — Tue, 13 Mar 2018 18:49:16 GMT

Try like this -

Table1:

CrossTable([Exam Type], Data)

LOAD Name,

[Exam 1],

[Exam 2],

[Exam 3],

Exam4

FROM

[https://community.qlik.com/thread/294611]

(html, codepage is 1252, embedded labels, table is @1);

Table2:

Load

[Exam Type],

Count(If (([Exam Type]='Exam 1' or [Exam Type]='Exam 2' or [Exam Type]='Exam 3' or [Exam Type]='Exam 4') and Data='Jan-18',Data)) as [Jan-18],

Count(If (([Exam Type]='Exam 1' or [Exam Type]='Exam 2' or [Exam Type]='Exam 3' or [Exam Type]='Exam 4') and Data='Feb-18',Data)) as [Feb-18],

Count(If (([Exam Type]='Exam 1' or [Exam Type]='Exam 2' or [Exam Type]='Exam 3' or [Exam Type]='Exam 4') and Data='Mar-18',Data)) as [Mar-18],

Count(If (([Exam Type]='Exam 1' or [Exam Type]='Exam 2' or [Exam Type]='Exam 3' or [Exam Type]='Exam 4') and Data='Apr-18',Data)) as [Apr-18],

Count(If (([Exam Type]='Exam 1' or [Exam Type]='Exam 2' or [Exam Type]='Exam 3' or [Exam Type]='Exam 4') and Data='May-18',Data)) as [May-18],

Count(If (([Exam Type]='Exam 1' or [Exam Type]='Exam 2' or [Exam Type]='Exam 3' or [Exam Type]='Exam 4') and Data='Jun-18',Data)) as [Jun-18],

Count(If (([Exam Type]='Exam 1' or [Exam Type]='Exam 2' or [Exam Type]='Exam 3' or [Exam Type]='Exam 4') and Data='Jul-18',Data)) as [Jul-18],

Count(If (([Exam Type]='Exam 1' or [Exam Type]='Exam 2' or [Exam Type]='Exam 3' or [Exam Type]='Exam 4') and Data='Aug-18',Data)) as [Aug-18],

Count(If (([Exam Type]='Exam 1' or [Exam Type]='Exam 2' or [Exam Type]='Exam 3' or [Exam Type]='Exam 4') and Data='Sep-18',Data)) as [Sep-18],

Count(If (([Exam Type]='Exam 1' or [Exam Type]='Exam 2' or [Exam Type]='Exam 3' or [Exam Type]='Exam 4') and Data='Oct-18',Data)) as [Oct-18],

Count(If (([Exam Type]='Exam 1' or [Exam Type]='Exam 2' or [Exam Type]='Exam 3' or [Exam Type]='Exam 4') and Data='Nov-18',Data)) as [Nov-18],

Count(If (([Exam Type]='Exam 1' or [Exam Type]='Exam 2' or [Exam Type]='Exam 3' or [Exam Type]='Exam 4') and Data='Dec-18',Data)) as [Dec-18],

Count(If (([Exam Type]='Exam 1' or [Exam Type]='Exam 2' or [Exam Type]='Exam 3' or [Exam Type]='Exam 4') and Data='Unknown',Data)) as [Unknown]

Resident Table1

Group By [Exam Type];

Drop table Table1;

Re: Values in a Table become a Column

Digvijay_Singh — Tue, 13 Mar 2018 19:01:43 GMT

Re: Values in a Table become a Column

MarcoWedel — Tue, 13 Mar 2018 22:52:12 GMT

Hi,

maybe one solution could be:

tabTemp:
CrossTable ([Exam Type],MonthTemp)
LOAD * FROM [https://community.qlik.com/thread/294611] (html, codepage is 1252, embedded labels, table is @1);

tabExams:
LOAD Name,
    [Exam Type],
    Date#(MonthTemp,'MMM-YY') as Month
Resident tabTemp
Where Len(MonthTemp);

DROP Table tabTemp;

tabMonths:
LOAD Date(MonthName(MinMonth,IterNo()-1),'MMM-YY') as Month
While MonthName(MinMonth,IterNo()-1) <= MaxMonth;
LOAD Min(Month) as MinMonth,
    Max(Month) as MaxMonth
Resident tabExams;

hope this helps

regards

Marco

Re: Values in a Table become a Column

kfahri342 — Wed, 14 Mar 2018 10:06:09 GMT

Thanks Marco.

But it doesn't seem to be working for me.

Issue 1. My Months come out in number format [i.e 42736,42767 etc.] not MMM-YY as desired,

Issue 2: The count doesn't seem to work. I receive the same number across all months. e.g. my database has 3000 entries, this value appear everywhere under each Exam Type.

Unfortunately, I cannot share what I am working on as this is for work. I hope the above makes sense.

Many Thanks

Kozan

Re: Values in a Table become a Column

MarcoWedel — Wed, 14 Mar 2018 20:41:15 GMT

Hi,

please try to upload a sample application that demonstrates the issue.

Just purge any unrelated objects and data and scramble fields that are confidential e.g. like described here:

Preparing examples for Upload - Reduction and Data Scrambling

Uploading a Sample

thanks

regards

Marco