Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

changing time format every day

Hello Everyone,

in this question/discussion I was given a solution to my time format problem (https://community.qlik.com/thread/309548)

"Time(Time#(TPTIPP, If(Len(Trim(TPTIPP)) = 5, 'hmmss', 'hhmmss'))) AS [Time-scan-to-bin-location-return]"

Now I have the issue, that I need to upload data every day and have to manually paste the solution into the script.

Is it possible to create a new section which will regonize that the field names (2 or more per section) in 2 different sections are the same and automatically create a synch key or something like it?

Really appreciate  your help

Tags (1)
9 Replies
Highlighted
Specialist
Specialist

Re: changing time format every day

Hi, James. It's not clear enough. Could you share your scipt?

Highlighted
Contributor III
Contributor III

Re: changing time format every day

Hi Andrey,

here is the script.

Marked in red are the fields, I have to change daily.

script 1.pngscript 2.png

Highlighted
Creator III
Creator III

Re: changing time format every day

Hi,

there will help simple update of script. Do you want to load all of excel files from your folder?

If yes, than do the following in from section (line 44)


From [lib://New LFS wider solution/*.xls]

(biff, embeded labels, table is [Sheet1])


Above will read all xls files in folder. If each has the same content and sheet in each xls has the same name than it will execute correctly. Actually as you have it from Data Manager there will be need to get rid of those table renaming stuff (rows 1-19 and row 69)

part with "...table is [there should be name of your sheet/table in xls] if you insert it via Data load editor than you will see what is "name of table" (most offten it is Sheet1).


Only one loading will be enought. You can delete the rest


Hope this help

Highlighted
Contributor III
Contributor III

Re: changing time format every day

Hi David,

should the script look like this?

script 3.png

Highlighted
Creator III
Creator III

Re: changing time format every day

Hi,

yes this is exactly what I meant . Does it work?

Highlighted
Contributor III
Contributor III

Re: changing time format every day

Unfortunately not.

Here is the Error Messaage:

Started loading data

The following error occurred:

Table 'SHEET 1' not found

The error occurred here:

[WE_ZEITEN 1$]:
LOAD
[TPNORT],
[TPVSST],
[BOL],
[TPWENR],
[TPIDEN],
[TPZUNV],
Date(Date#([TPDTEE], 'YYYYMMDD') ) AS [TPDTEE],
[FLAG],
[TPNBER],
[TPNREG],
[TPNHOR],
Date(Date#([TPDTA], 'YYYYMMDD') ) AS [TPDTA],
Time(Time#(TPTIA, If(Len(Trim(TPTIA)) = 5, 'hmmss', 'hhmmss'))) AS [TPTIA],
Date(Date#([TPDTPP], 'YYYYMMDD') ) AS [TPDTPP],
Time(Time#(TPTIPP, If(Len(Trim(TPTIPP)) = 5, 'hmmss', 'hhmmss'))) AS [TPTIPP],
[TPAUSR],
[TPABS]
FROM [lib://New LFS wider solution/*.xls]
(biff, embedded labels, table is [SHEET 1])

Data has not been loaded. Please correct the error and try loading again.

The excel file looks like this:

script 4.png

Highlighted
Creator III
Creator III

Re: changing time format every day

That is obviously because of sheet name. So you get each day new excel file with two sheets and you want to load both of them? Is format allways the same? Is there every time sheet WE_ZEITEN 1 and WE_ZEITEN 2 ?

Highlighted
Contributor III
Contributor III

Re: changing time format every day

I get an excel file every day. The excelfile will get more sheets depending on the amount of data from core system.

Next week the excel will have propably 3 sheets and the week after 4. (But this depends on the workload) 

The format is always the same.

Highlighted
Creator III
Creator III

Re: changing time format every day

So if I uderstand well, you will have only one excel file (important think is that it is .xls) you want to load with multiple sheets. For that case try something like that.

Ignore Excel Sheet Name

need to load all the sheets in excel at time ?

Loading Multiple Excel Sheets Dynamically along with file name and sheet name