topic Re: Issue with Date in QlikView

Issue with Date

Thu, 18 Feb 2016 04:49:23 GMT

Hi All, I have an excel file containing attendance records of all employees in the format specified below.

Employee #	Names	Team Name	Designation	Work status	01-Jan-16 Fri	02-Jan-16 Sat	03-Jan-16 Sun	04-Jan-16 Mon	05-Jan-16 Tue	06-Jan-16 Wed	07-Jan-16 Thu
1	Priya	IT	Analyst	Active	P	P	P	P	P	P	P
2	Priyanka	IT	Analyst	Active	A	A	A	A	P	P	P
3	Monika	Finance	Analyst	Active	P	P	P	P	P	P	P
4	Sarvesh	Admin	Analyst	Active	P	P	P	P	P	P	P

And I want it to load it in below format

Employee #	Names	Team Name	Designation	Work status	Date	Att_Value
1	Priya	IT	Analyst	Active	01/01/2016	P
2	Priyanka	IT	Analyst	Active	01/01/2016	A
3	Monika	Finance	Analyst	Active	01/01/2016	P
4	Sarvesh	Admin	Analyst	Active	01/01/2016	P
1	Priya	IT	Analyst	Active	02/01/2016	P
2	Priyanka	IT	Analyst	Active	02/01/2016	A
3	Monika	Finance	Analyst	Active	02/01/2016	P
4	Sarvesh	Admin	Analyst	Active	02/01/2016	P
1	Priya	IT	Analyst	Active	03/01/2016	P
2	Priyanka	IT	Analyst	Active	03/01/2016	A
3	Monika	Finance	Analyst	Active	03/01/2016	P
4	Sarvesh	Admin	Analyst	Active	03/01/2016	P
1	Priya	IT	Analyst	Active	04/01/2016	P
2	Priyanka	IT	Analyst	Active	04/01/2016	A
3	Monika	Finance	Analyst	Active	04/01/2016	P
4	Sarvesh	Admin	Analyst	Active	04/01/2016	P
1	Priya	IT	Analyst	Active	05/01/2016	P
2	Priyanka	IT	Analyst	Active	05/01/2016	P
3	Monika	Finance	Analyst	Active	05/01/2016	P
4	Sarvesh	Admin	Analyst	Active	05/01/2016	P
1	Priya	IT	Analyst	Active	06/01/2016	P
2	Priyanka	IT	Analyst	Active	06/01/2016	P
3	Monika	Finance	Analyst	Active	06/01/2016	P
4	Sarvesh	Admin	Analyst	Active	06/01/2016	P
1	Priya	IT	Analyst	Active	07/01/2016	P
2	Priyanka	IT	Analyst	Active	07/01/2016	P
3	Monika	Finance	Analyst	Active	07/01/2016	P
4	Sarvesh	Admin	Analyst	Active	07/01/2016	P

But the issue I am facing is with the date column. It is getting loaded in number format. I tried converting it into date format using date and date# function but couldn't achieve desired result.

I need your suggestions.

Thanks in advance

-Priyanka

Re: Issue with Date

ajsjoshua — Thu, 18 Feb 2016 04:53:44 GMT

Hi,

while loading the excel after clicking the Embedded labels click Cross Table

and enter the Qualifier fields ,attribute fields and data field.

Re: Issue with Date

sasikanth — Thu, 18 Feb 2016 04:53:55 GMT

For the desired format use CrosTable load

Cross Table(Date,Att_value,5)

Load * from Excel;

Please attach sample data

Re: Issue with Date

tresB — Thu, 18 Feb 2016 04:55:41 GMT

Using Crosstable(), you could get that. Try to share a sample excel which we can work upon.

Re: Issue with Date

Thu, 18 Feb 2016 05:09:35 GMT

Tresesco - I am not getting option to attach sample file.

Re: Issue with Date

tresB — Thu, 18 Feb 2016 05:12:14 GMT

Click on Reply-> Use advanced editor (top right) ->Attach (bottom right)

Re: Issue with Date

Thu, 18 Feb 2016 05:17:27 GMT

Hi,

I am not getting Attach option.please see below screenshot.

Re: Issue with Date

tamilarasu — Thu, 18 Feb 2016 05:19:04 GMT

Hi,

Check the attachment.

Edit: Sorry, Missed to change the date format. Attached new file against your data.

Re: Issue with Date

tresB — Thu, 18 Feb 2016 05:23:22 GMT

It seems the option is not somehow not appearing for you. You might have to contact community admin CommunityAdmin‌ or sli‌

Re: Issue with Date

Chanty4u — Thu, 18 Feb 2016 05:32:15 GMT

u can open ur discussion in seperate tab then only u will find right side top advnced editor

right bottom u will ffind attach

Re: Issue with Date

Thu, 18 Feb 2016 05:40:50 GMT

Hi Nagaraj,

When I am trying to load my data, its in this format

Attendence_Staging:
CrossTable([Report Date], Attendence_Value, 5)
LOAD Num#([Employee #]) as [Employee No],
Names as [Employee Names],
[Team Name],
Designation,
[Work status],
[42370],
[42371],
[42372],
[42373],
[42374],
[42375],
[42376]

FROM $(vAttendence)Attendance tracker UK Ops.xlsx
(ooxml, embedded labels, table is [Jan - Dec 2016]) ;

Below is the screenshot of Report Date field

My QV date format is

SET DateFormat='DD-MM-YYYY';
SET TimestampFormat='DD-MM-YYYY hh:mm:ss[.fff]';

when I am using below script

Data:
Load *,
Date(Date#(Left([Report Date],7) & '2016','DD/MM/YYYY'),'DD-MM-YYYY') as Date
Resident Attendence_Staging;

I am getting Date field as '-'.

Please help

-Priyanka

Re: Issue with Date

tresB — Thu, 18 Feb 2016 05:40:51 GMT

Or is it - you are trying to reply from your Inbox? Inbox doesn't have that option, try to open the thread in browser separately, you should get to attach.

Re: Issue with Date

Thu, 18 Feb 2016 05:46:39 GMT

Tresesco- Yes I was trying to reply from inbox and that's the reason I was not getting attach option. Thanks for notifying it. I have attached sample excel file for your reference.

Re: Issue with Date

sasikanth — Thu, 18 Feb 2016 05:47:32 GMT

Try this

Date(Date#(Report_date,'DD-MMM-YYYY'),'DD-MMM-YYYY') as Req_Date

Re: Issue with Date

tamilarasu — Thu, 18 Feb 2016 05:49:17 GMT

Priyanka,

Try like below,

Tmp:
CrossTable([Report Date], Attendence_Value, 5)
LOAD  Num#([Employee #]) as [Employee No], 
Names as [Employee Names], 
[Team Name], 
Designation, 
[Work status], 
[42370], 
[42371], 
[42372], 
[42373], 
[42374], 
[42375], 
[42376]
FROM $(vAttendence)Attendance tracker UK Ops.xlsx
(ooxml, embedded labels, table is [Jan - Dec 2016]) ;


Data:
Load *,
Date(Num#([Report Date])) as Date
Resident Attendence_Staging;


Drop Field [Report Date];
Drop Table Tmp;

Re: Issue with Date

tresB — Thu, 18 Feb 2016 05:57:09 GMT

Temp:

CrossTable(Date, Att_Value, 5) LOAD [Employee #],

     Names,

     [Team Name],

     Designation,

     [Work status],

     [42370],

     [42371],

     [42372],

     [42373],

     [42374],

     [42375],

     [42376]

FROM

(ooxml, embedded labels, table is Sheet1);

Load

Names,

[Employee #],

[Team Name],

     Designation,

     [Work status],

     Date(PurgeChar(Date,'[]')) as Date

Resident Temp     ;

Drop Table Temp;

Re: Issue with Date

MarcoWedel — Thu, 18 Feb 2016 22:54:53 GMT

Hi,

in order to avoid hard coded date values in your script (e.g. for changing source files) you could try like:

table1:
CrossTable (DateTemp, Value, 6)
LOAD Evaluate([Employee #]) as Employee#, *
FROM [https://community.qlik.com/servlet/JiveServlet/download/977302-211779/Sample.xlsx] (ooxml, embedded labels, table is Sheet1);

Left Join (table1)
LOAD Distinct
    DateTemp,
    Date(Evaluate(DateTemp)) as Date
Resident table1;

DROP Fields [Employee #], DateTemp;

hope this helps

regards

Marco

Re: Issue with Date

Mon, 22 Feb 2016 04:38:27 GMT

Hi Marco,

Thanks for your reply.

Can you please help me out in understanding this script. i.e. "LOAD Evaluate([Employee #]) as Employee#, *" why we have used Evaluate() in this script.

Thanks in advance

Re: Issue with Date

sujeetsingh — Mon, 22 Feb 2016 05:51:11 GMT

Use Crosstable Load

Re: Issue with Date

Anonymous — Mon, 22 Feb 2016 06:23:25 GMT

By using the crosstable you can get the result