topic Re: Adding Date field to table from same Excel file in QlikView

Adding Date field to table from same Excel file

dannysci — Fri, 20 Nov 2020 18:58:42 GMT

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

Re: Adding Date field to table from same Excel file

marcus_sommer — Mon, 09 Nov 2020 11:06:24 GMT

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

Re: Adding Date field to table from same Excel file

Vegar — Mon, 09 Nov 2020 11:15:06 GMT

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')

Re: Adding Date field to table from same Excel file

dannysci — Mon, 09 Nov 2020 12:35:38 GMT

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