Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

two questions should a text file work like an excel file as far a date time stamps? I have a deticated fold for exact maching files

I have reports that are emailed to me weekly the field headings are exactly the same.  Can I just add it to a folder and then just refresh my APP to included the new file into the app?

The file comes to me as a TXT file.  It has two datetimestamps that I am comparing, do I need to convert the files to excel to get the duration in my sheet?

8 Replies
Lech_Miszkiewicz
Partner Ambassador/MVP
Partner Ambassador/MVP

Assuming that text files are having the same structure you should only do following:

  • paste files in the same location
  • make sure "FROM" statement in your script has a sort of wildcard characters in it so multiple files can be read. for example
    • report201605.txt
    • report201606.txt
  • so your FROM statement has to look like (note *):
    • From [lib://MyConnection/report*.txt]
  • as a good practice i also create usually one extra field in my load script which indicates from which file data come from
    • FileBaseName() as Source

or

    • FileName() as Source

HTH

Lech

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.
Lech_Miszkiewicz
Partner Ambassador/MVP
Partner Ambassador/MVP

Sorry i missed your second question:

so regarding timestamps it is just important that the are recognized as a timestamps. It would be helpful if you could just provide how they are written in your files, so i can determine what formatting the are having 12 or 24 hrs with AM/PM or without etc...

for now lets assume you have 2 timestamps (to make it more complicated i am just on purpose showing them in 2 different formats)

  • timestamp 1 = 2016/12/06 13:13:55
  • timestamp 2 = 06-12-2016 8:15:45 PM

So in your script you have to make sure that the are read as timestamps in the same formats. You can do following then:

  • for first timestamp do: Timestamp(Timestamp#(timestamp1, 'YYYY/MM/DD  hh:mm:ss')) as timestamp1,
  • for secondtimestamp do: Timestamp(Timestamp#(timestamp2, 'MM-DD-YYYY  hh:mm:ss TT')) as timestamp2,
  • you can also create another field calculating difference between them in load script or just do it on front end expression

regards

Lech

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.
Not applicable
Author

The file part worked great but the timestamp is well not working any idea?

Lech_Miszkiewicz
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi I guess it just finding what formats are those dates held in. Could you  please provide sample data so i can work it out for you?

cheers

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.
Not applicable
Author

How is this?

Lech_Miszkiewicz
Partner Ambassador/MVP
Partner Ambassador/MVP

Well

as you can see format of timestamps in this file is not YYYY-MM-DD but MM/DD/YYYY.

So please change your script for all timestamp fields to something like:

Timestamp(Timestamp#(timestamp1, 'MM/DD/YYYY  hh:mm'))


I am ot sure how your time is held there as there is no indication whether it is AM or PM in timestamp field bu you should get away just withi this code


let me know how it goes!


cheers

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.
Not applicable
Author

Still cant get that date thing working?

LOAD
    OrderDate,
    OrderNumber,
    FacilityName,
    FacilityID,
    RouteNumber,
    Timestamp (ScheduledLeaveTime, 'MM-DD-YYYY  hh:mm') as ScheduledLeaveTime, 
    Timestamp (ActualLeaveTime, 'MM-DD-YYYY  hh:mm') as ActualLeaveTime

FROM [lib://HealthEX (pharmore_rwinkel)/*.txt]
(txt, codepage is 1252, embedded labels, delimiter is '\t', msq)

Lech_Miszkiewicz
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi

You are missing very important part of Timestamp# statement which is #.

Compare my & yours code.


Timestatmp# converts text to timestamp where Timestamp function without # converts timestamp to format you want to looka at it.

I made example for you - see attached - cheers

Lech

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.