topic Re: How to load a table with month stored above headers? in QlikView

How to load a table with month stored above headers?

Redcoal — Wed, 08 Jul 2020 12:31:12 GMT

Hi QV Experts, my first post and new to QV scripting. I am trying to load the below sample data with the "month-year" under Header 5 (as Month), every line to repeat (Sep-19) but unsure how to script this in the code.

Loading the headers and the data content is easy.

I have multiple tables/files to load across in the same format too.

Appreciate the help! Cheers

			Sep-19
Title
Header 1	Header 2	Header 3	Header 4
a	1	2	4
b	2	3	5
c	3	4	6
d	4	5	7

Re: How to load a table with month stored above headers?

Taoufiq_Zarra — Wed, 08 Jul 2020 13:04:42 GMT

If I understood correctly ,one solution:

Suppoose My Input file is and excel with :

So initial header A,B,C,D

Data: First 1 load Date(RangeMax(A,B,C,D),'MMM-YY') as Date FROM .\Classeur1.xlsx (ooxml, explicit labels, table is Feuil1); left join load [Header 1], [Header 2], [Header 3], [Header 4] FROM .\Classeur1.xlsx (ooxml, embedded labels, header is 2 lines, table is Feuil1);

you can change A,B,C,D by your header initial

output :

Re: How to load a table with month stored above headers?

Redcoal — Thu, 09 Jul 2020 00:27:43 GMT

Thanks for the recommendation 'Taoufiq', I wasn't able to get it to work.

I followed your method but couldn't make out the RangeMax function as is essentially cell D1. I will be combining multiple files, so that date will change for each file..

I used the table function to remove rows/columns I don't need in order to get the cell I want. Is ugly in the codes but is sort of producing what i wanted 🙂

Another small complexity I am trying to tackle is cell D1 is stored as 'Text'. The field is actually 'September 2019'. So will need to somehow convert this to a readable date/month function.

Any tips on coverting the text? I have been reading up from the below but not sure how to get it to work from my [Month Year] field.

https://community.qlik.com/t5/New-to-QlikView/Convert-text-to-date-format/td-p/98370

Re: How to load a table with month stored above headers?

Taoufiq_Zarra — Thu, 09 Jul 2020 08:20:59 GMT

to convert 'September 2019' to date :

First Add :

Set LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';

then you can use :

=Date(Date#('September 2019','MMMM YYYY'),'NEWFORMAT')

NEWFORMAT is the expected date format

for exemple :

=Date(Date#('September 2019','MMMM YYYY'),'MM-YY') the output is :

Re: How to load a table with month stored above headers?

Redcoal — Mon, 13 Jul 2020 01:48:13 GMT

Your method works for me.

Thanks for your help!