Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Timestamp and or data Load problem

Timestamp Problem.

I have two date formats in a CSV do not play happily with the Timestamp (the application that it comes from does not export .xlsx so it is row limited).

If you notice the dates below have a differing number of blanks between the year and time depending on the number of characters in the hour.

1/3/2011   7:02:38AM

2/22/2011  12:22:20PM

I have got round this by replacing 2011 with three trailing blanks with 2011, 2 blanks and a 0 that is appending a 0 onto the hour if it’s less than 2 digits. If the Timestamp could be resolved my second problem goes away.

LOAD problem.

I do the date manipulation (replace) in the first LOAD statement and have to run a second LOAD using existing data which creates my second problem. I end up with two tables Data and Data-1 that are somehow unconnected.  It seems that nothing maps correctly if done this way whereas if I do the replace using notepad and load the data in one pass it all works.

Data:LOAD replace(@17,'Agent Name:','') as Agent_Name,
    
replace(@25,'/2011   ','/2011 0')  as Start_Session_Time_sub,
    
replace(@26,'/2011   ','/2011 0')  as End_Session_Time_sub,
    
@27 as Time_In_Session,
    
@29 as Availability,
    
@30 as Unavailability_State,
    
@31 as State,
    
replace(@32,'/2011   ','/2011 0')  as Start_State_Time_sub,
    
replace(@33,'/2011   ','/2011 0')  as End_State_Time_sub,
    
Interval#(@34,'hh:mm:ss') as Time_In_State        FROM

(
txt, codepage is 1252, no labels, delimiter is ',', msq);
LOAD
   
Date(Timestamp(timestamp#(Start_Session_Time_sub,'MM/DD/YYYY  hh:mm:ssTT'))) as Start_Session_Date,
   
Day(Timestamp(timestamp#(Start_Session_Time_sub,'MM/DD/YYYY  hh:mm:ssTT'))) as Start_Session_Day,
   
WeekDay(Timestamp(timestamp#(Start_Session_Time_sub,'MM/DD/YYYY  hh:mm:ssTT'))) as Start_Session_WeekDay,
   
Month(Timestamp(timestamp#(Start_Session_Time_sub,'MM/DD/YYYY  hh:mm:ssTT'))) as Start_Session_Month,
   
Timestamp(timestamp#(Start_Session_Time_sub,'MM/DD/YYYY  hh:mm:ssTT')) as Start_Session_Time,
   
Timestamp(timestamp#(End_Session_Time_sub,'MM/DD/YYYY  hh:mm:ssTT')) as End_Session_Time,
   
Timestamp(timestamp#(Start_State_Time_sub,'MM/DD/YYYY  hh:mm:ssTT')) as Start_State_Time,
   
Timestamp(timestamp#(End_State_Time_sub,'MM/DD/YYYY  hh:mm:ssTT')) as End_State_Time
resident Data;

Perhaps I don't quite understand the Load - how to get it into the same table or flexibility/syntax around timestamp. Appreciate your help.

Thanks

David

Uploaded qvw's and csv's. The one with Works is the set with the edited CSV. The demo is the one I'm trying to load raw and convert on the fly - you will see that it is not picking up or being able to sum properly by day. Thanks

1 Solution

Accepted Solutions
danielrozental
Honored Contributor II

Re: Timestamp and or data Load problem

Take a look at this

5 Replies
danielrozental
Honored Contributor II

Timestamp and or data Load problem

David, it will probably easier to help you if you share a sample csv with just some rows and the qvw application you are using.

nagaiank
Valued Contributor III

Re: Timestamp and or data Load problem

I used the following script to get the table displayed below:

LOAD *, Day(Date(DateNum)) as Day, WeekDay(Date(DateNum)) as WeekDay, Month(Date(DateNum)) as Mon;

LOAD *, Floor(TSNum) as DateNum, SubField(CsvData,' ',2) as TimePart;

LOAD *, Num(Timestamp#(CsvData,'MM/DD/YYYY hh:mm:ssTT')) as TSNum;

LOAD * Inline [

CsvData

1/3/2011 7:02:38AM

2/22/2011 12:22:20PM

];

New Bitmap Image.bmp

In order to take care of varying number of blanks between year and hour, you may try something like

replace(raplace(CsvData,'  ',' '),'  ',' ')

Hope this helps to arrive at your load script.

Not applicable

Timestamp and or data Load problem

Added qvw's/csv's to the post

danielrozental
Honored Contributor II

Re: Timestamp and or data Load problem

Take a look at this

Not applicable

Timestamp and or data Load problem

Thanks Daniel.

Works a charm.

Community Browser