Adding Date field to table from same Excel file

[dannysci]

Hello Community,

Every day I receive an Excel file with the turnover of the day. The only problem is that the date is on the first line, and I want to add it to a table and then read it in with the rest of the tables in the same file, so far I haven't been able to get it, so please help!

The columns and row in the Excel file look like this

Résultat du samedi 07 novembre 2020
Période	Enjeux	Marge Th.	Lim. Basse	Rapports	MoneyRoom	Marge Réelle	Solde

But should look like this in Qlikview

Période

Enjeux

Marge Th.

Lim. Basse

Rapports

MoneyRoom

Marge Réelle

Solde

Date

I added the files I worked with, the tryouts are in the qvw file. But with every attempt, I saw the values double in the end result.

Cheers, Danny

[marcus_sommer]

You may use something like this:

temp: first 1 load date(date#(replace(A, 'Résultat du samedi ', ''), 'DD MMMM YYYY')) as temp
from Excel (ooxml, no labels, table is Sheet1);

final: load *, peek('temp', 0, 'temp') as Date
from Excel (ooxml, embedded labels, table is Sheet1, header is 2 lines);

drop table temp;

For the converting of the string into a real date it's important to specify the exact format-pattern which must also fit with the default-variables for the data-interpreting at the beginning of the script. If they are mandatory different you may use some more string/pick-functions to fill a makedate() with them.

- Marcus

[Vegar · Accepted solution]

Check out my attached solution.

for each _file in filelist ('./Resultaat du*.xlsx')

MapMonth:
MAPPING LOAD * inline [
M_txt, M_no
novembre, 11
décembre, 12
];

FilePeriod:
first 1 LOAD
Makedate( SubField(A,' ',-1), applymap('MapMonth',SubField(A,' ',-2)) , SubField(A,' ',-3)) as Period
FROM
[$(_file)]
(ooxml, no labels, table is Sheet1);

LET vDate = peek('Period',-1,'FilePeriod'); //You will need to convert this into a date by some means 

DROP TABLE FilePeriod;
Result:
LOAD Période, 
     Enjeux, 
     [Marge Th.], 
     [Lim. Basse], 
     Rapports, 
     MoneyRoom, 
     [Marge Réelle], 
     Solde,
     '$(vDate)' as %date
FROM
[$(_file)]
(ooxml, embedded labels, header is 1 lines, table is Sheet1)
WHERE(Période <> 'Total' And not IsNull(Enjeux));

LET vDate=;
next

call Calendar ('%date')

[dannysci]

Thank you Vegar for the quick reply,  this solution works great!