changing time format every day

[jamesehunt]

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

[andrey_krylov]

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

[jamesehunt]

Hi Andrey,

here is the script.

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

[DavidŠtorek]

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

[jamesehunt]

Hi David,

should the script look like this?

[DavidŠtorek]

Hi,

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

[jamesehunt]

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:

[DavidŠtorek]

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 ?

[jamesehunt]

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.

[DavidŠtorek]

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