topic Re: Date Issues with Crosstab Function in QlikView

Date Issues with Crosstab Function

Wed, 06 May 2015 19:14:01 GMT

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.

Re: Date Issues with Crosstab Function

Anonymous — Wed, 06 May 2015 19:25:47 GMT

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?

Re: Date Issues with Crosstab Function

ramoncova06 — Wed, 06 May 2015 19:34:59 GMT

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 ?

Re: Date Issues with Crosstab Function

Wed, 06 May 2015 19:55:01 GMT

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.

Re: Date Issues with Crosstab Function

petter — Wed, 06 May 2015 20:47:56 GMT

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;

Re: Date Issues with Crosstab Function

ramoncova06 — Wed, 06 May 2015 22:03:00 GMT

Crosstable is giving me the same results that you are getting

try with the attached qvw, it does work for me

Re: Date Issues with Crosstab Function

petter — Wed, 06 May 2015 22:11:10 GMT

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?

Re: Date Issues with Crosstab Function

ramoncova06 — Wed, 06 May 2015 22:12:42 GMT

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

Re: Date Issues with Crosstab Function

petter — Wed, 06 May 2015 22:14:16 GMT

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

Re: Date Issues with Crosstab Function

ToniKautto — Wed, 06 May 2015 22:23:56 GMT

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.

Re: Date Issues with Crosstab Function

petter — Wed, 06 May 2015 22:27:43 GMT

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...

Re: Date Issues with Crosstab Function

ToniKautto — Wed, 06 May 2015 22:32:45 GMT

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

Re: Date Issues with Crosstab Function

MarcoWedel — Wed, 06 May 2015 23:22:25 GMT

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

Re: Date Issues with Crosstab Function

Thu, 07 May 2015 13:06:34 GMT

Question,

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

Re: Date Issues with Crosstab Function

petter — Thu, 07 May 2015 14:12:07 GMT

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.

Re: Date Issues with Crosstab Function

Thu, 07 May 2015 15:06:18 GMT

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:

Re: Date Issues with Crosstab Function

Thu, 07 May 2015 15:41:51 GMT

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.

Re: Date Issues with Crosstab Function

sasiparupudi1 — Sat, 10 Mar 2018 10:48:34 GMT

Please check

Rename Column Names Programatically in the Qlikview Script