Date Issues with Crosstab Function

[#1]

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.

[petter]

The NOCONCATENATE shouldn't be necessary since I suggested a DROP TABLE as the last statement. The tables won't auto concatenate anyway since one of the fields are different... DATE in the first table and Date in the second...

[ToniKautto]

Thanks Petter, I stand corrected. I missed the change of case in the field name!

[MarcoWedel]

Hi,

to generate generic code, try to avoid explicit timestamp values (column names) by loading all fields instead using '*'.

So one solution could be:

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='hh:mm:ss';
SET DateFormat='MM/DD/YYYY';
SET TimestampFormat='MM/DD/YYYY hh:mm:ss[.fff]';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

SET LongMonthNames = 'January;February;March;April;May;June;July;August;September;October;November;December';
SET LongDayNames = 'Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';

tabEmailUsage:
CrossTable(DateTemp, Data, 2)
LOAD *
FROM [https://community.qlik.com/thread/163032] (html, codepage is 1252, embedded labels, table is @1);

Left Join (tabEmailUsage)
LOAD Distinct
  DateTemp,
  Date(Date#(Trim(Mid(DateTemp,Index(DateTemp, ',')+1)),'MMMM DD, YYYY'), 'WWWW, MMMM DD, YYYY') as Date
Resident tabEmailUsage;

DROP Field DateTemp;

(Group values are not correct here, because the group column in your example table contained empty values)

hope this helps

regards

Marco

[#5]

Question,

In the nonconcatenate load, where does the Opened Mail come from?

[petter]

You see it being introduced in the CrossTable:

CROSSTABLE(DATE,OpenedMail,2) 

The first two parameters specify the two new fields that needs to be created to keep the unpivoted data. The DATE field and the OpenedMail field. The last parameter tells QlikView how many row label columns it needs to keep untouched before it finds the data to unpivot.

[#7]

This does not seem to work (I'm testing all the other recommendations listed too).

When I write it like this the Date filed now looks like this:

[#8]

Wedel Marco,

I used your qvw file and just modified it for my file location and I still do not get what you get.  Note, I first went and filled in all the blanks for the group so that every user had a gour associated with it (i.e., no blanks anymore).  I get the same thing I posted previously (no dates like you have in your example).

Sorry I did not attach table, but I keep getting an error message when I try to attach and it shuts down my internet explorer.

Thanks.

[sasiparupudi1]

Please check

Rename Column Names Programatically in the Qlikview Script