Re: Date Issues with Crosstab Function - Qlik Community

Report Inappropriate Content · ‎2015-05-06

I was wondering if someone could help with this crosstab creation. I have a table similar to the following and need to make sense of the data in it.

Group	User	Thursday, January 01, 2015	Friday, January 02, 2015	Monday, January 05, 2015	Tuesday, January 06, 2015
Group 1	hunt	1	1	1	1
	clark
	heim		1
	shaw
Group 2	hunt	1
	smith	1
	albertson		1		1
	shaw
Group 3	cwehunt			1
	fclark
	jones	1	1
	parks		1
	wills
Group 4	thompson	1			1
	hunter			1	1
	last

The table lets me know how if a user opened their email on the particular date or not. For example, hunt from Group1 (not Group2, although it is the same user) opened the email Jan 1, Jan 2, Jan 5 and Jan 6. For these purposes, I only show these four dates, but the dates are really from 01/01/2015 to present.

When I load this excel table into QV, the date columns turn into the serial number and I do not know how to get these to go back to actual dates. See below.

LOAD

Group,
User,

     [42005],

     [42006],

     [42009],

     [42010],

     [42011],

     [42012],

     [42013],

     [42016],

     [42017],

     [42018],

     [42019],

     [42020],

     [42023],

     [42024],

     [42025],

     [42026],

     [42027],

     [42030],

     [42031],

     [42032],

     [42033],

     [42034],

     [42037],

     [42038],

     [42039],

     [42040],

     [42041],

     [42044],

     [42045],

     [42046],

     [42047],

     [42048],

     [42051],

     [42052],

     [42053],

     [42054],

     [42055],

     [42058],

     [42059],

     [42060],

     [42061],

     [42062],

     [42065],

     [42066],

     [42067],

     [42068],

     [42069],

     [42072],

     [42073],

     [42074],

     [42075],

     [42076],

     [42079],

     [42080],

     [42081],

     [42082],

     [42083],

     [42086],

     [42087],

     [42088],

     [42089],

     [42090],

     [42093],

     [42094],

     [42095],

     [42096],

     [42097],

     [42100],

     [42101],

     [42102],

     [42103],

     [42104],

     [42107],

     [42108],

     [42109],

     [42110],

     [42111],

     [42114],

     [42115],

     [42116],

     [42117],

     [42118],

     [42121],

     [42122],

     [42123]

FROM

(ooxml, embedded labels, table is Combo2);

1. How do I get QV to recognize these as a date?

2. Is there a way to have all these date columns be recognized as one dimension, i.e., DATE?

I tried doing this as a crosstab, with Group and User as the qualifier fields and renamed the attribute field DATE, but I still could not resolve the date formatting issue and then I cannot tell what group each user was attributed to.

Any help is greatly appreciated.

Anonymous · ‎2015-05-06

If the column names in the data source are in this format, it is fine, you can convert to any date format after loading using crosstable. You'll need something lie this:

date(DATE, 'WWWW, MMMM DD, YYYY')

But looks like something else is missing. Can you upload the app to see what exactly?

ramoncova06 · ‎2015-05-06

it seems as you are applying the cross tab in excel, is this right ?

or can you show us how are you doing the crosstable ?

Report Inappropriate Content · ‎2015-05-06

I am not sure if this is what you mean, but this is my load statement. The table is already in that format from Excel.

petter · ‎2015-05-06

You can probably do this:

MailStatus:

CROSSTABLE(DATE,OpenedMail,2)

LOAD

Group,

User,

[42005],

[42006],

[42007],

[42008],

[42009],

[42010],

[42011],

[42012],

[42013],

[42014],

[42015],

[42016],

[42017],

[42018],

[42019],

[42020],

[42021],

[42022],

[42023],

[42024],

[42025],

[42026],

[42027],

[42028],

[42029],

[42030],

[42031],

[42032],

[42033],

[42034],

[42035],

[42036],

[42037],

[42038],

[42039],

[42040],

[42041],

[42042],

[42043],

[42044],

[42045],

[42046],

[42047],

[42048],

[42049],

[42050],

[42051],

[42052],

[42053],

[42054],

[42055],

[42056],

[42057],

[42058],

[42059],

[42060],

[42061],

[42062],

[42063],

[42064],

[42065],

[42066],

[42067],

[42068],

[42069],

[42070],

[42071],

[42072],

[42073],

[42074],

[42075],

[42076],

[42077],

[42078],

[42079],

[42080],

[42081],

[42082],

[42083],

[42084],

[42085],

[42086],

[42087],

[42088],

[42089],

[42090],

[42091],

[42092],

[42093],

[42094],

[42095],

[42096],

[42097],

[42098],

[42099],

[42100],

[42101],

[42102],

[42103],

[42104],

[42105],

[42106],

[42107],

[42108],

[42109],

[42110],

[42111],

[42112],

[42113],

[42114],

[42115],

[42116],

[42117],

[42118],

[42119],

[42120],

[42121],

[42122],

[42123]

FROM

(ooxml, embedded labels, table is Combo2);

LOAD

Group,

User,

Date(DATE) AS Date,

OpenedMail

RESIDENT

MailStatus;

DROP TABLE MailStatus;

ramoncova06 · ‎2015-05-06

Crosstable is giving me the same results that you are getting

try with the attached qvw, it does work for me

petter · ‎2015-05-06

There is no problem with the field names being called [42117] and so forth... they will in the last load be converted into real dates - look at the line in my example above with:

Date(DATE) AS Date

It will take the DATE field from the CROSSTABLE and convert it into a Date ...

Isn't that what you needed?

ramoncova06 · ‎2015-05-06

I didn't try your solution Peter, I was already working on mine and I like people to have options

petter · ‎2015-05-06

I wasn't actually trying to respond to you but I was clarifying my solution to Karen ...

ToniKautto · ‎2015-05-06

In each application you have the format variable at the top of your script. My recommendation is that you start by making sure theses match your intended format for the application. For example if you want to maintain the incoming format like "Thursday, January 01, 2015" your date format variable would look like 'WWWW, MMMM DD, YYYY'.

As a side note, the date format and the time format is also represented in the TimestampFormat variable. My recommendation is that you make sure that these to are aligned so that your application has a uniform presentation format. To only have to maintain your format in the DateFormat and TimeFormat variables, you can expand their values into the TimestampFormat variable.

SET DateFormat='WWWW, MMMM DD, YYYY';
SET TimeFormat='hh:mm:ss';
SET TimestampFormat='$(DateFormat) $(TimeFormat)';

A date value is represented by an integer, and this is why your field label turn up as integer values. As an example 42122 is equal to 28 April 2015. There is no need to worry about the numerical field names in this case. You can see this by putting the expression =date(42122) in a text object.

There is no way that you can control the field name format within the cross table function. So you will have to resolve it with a construction like Petter suggested above. However, I think there is a need to use the NoConcatenate prefix on the second load to make it work.