Load different amount auf columns for each table

[Newhaven]

Hi!

I have an Excel for each year. Contains tables for each month.

I’m loading this with following script – works fine:

set Monate = 'Jän','Feb','Mär','Apr','Mai','Jun','Jul','Aug','Sep','Okt','Nov','Dez';

for Jahr = 2020 to  year(Today())
	for each Monat in $(Monate)
	
Date:
	LOAD
		UserName,
		'$(Jahr)' as Jahr,
		'$(Monat)' as Monat,
		date(MakeDate($(Jahr),match('$(Monat)', $(Monate)),1),'MMM YYYY') as MonatJahr
	FROM $(path6)\MA WU $(Jahr).xls (biff, embedded labels, header is 3 lines, table is [$(Monat)$]);

	next Monat 
next Jahr

But in Line 3 there are also columns for each day with a value for the UserName.

Problem, only the days which are possible for each month (Jän 2021: 1-31, Feb 1-28, …)

How could I load the columns when there are different amount of columns for each month?

Tx.

[marcus_sommer]

You may add between the second for-loop and the load-statement another condition which queries the current month-value and returned within a variable the appropriate columns and is afterwards used in the load. If the column-header are just the day you may add a forced concatenate-statement to load everything into a single table - otherwise you will get a separate table for each different table-structure.

IMO better will be to load the data with a wildcard * and to transform them in the same iteration per crosstable into a normal table-structure. It's not only easier to load the data with such an approach else the data-structure is much more practicably for nearly all further measurements within the script as well as the UI. To what is meant see:

The Crosstable Load - Qlik Community - 1468083

- Marcus

[Newhaven]

Like the idea for the crosstable.

Works fine if I load one table from the excel (example Jän)

But I've no idea how to get the crosstable in my loop an create my month and year field from the table an de filename.

[marcus_sommer]

I think I would just store the table within YYYYMM.qvd's like:

for ...
   for ...
      Date: load ... from ...;
      Crosstable: load ... resident Date;
      store Crosstable into [Crosstable_$(Jahr)$(Monat).qvd] (qvd);
      drop tables Date, Crsstable;
   next
next

And later you load those periods where you need them from the qvd's. You may do everything within a single qvw but personally I prefer a multi-tier data-architecture with an extracting/transforming layer to create the qvd's, a datamodel-layer loading multiple qvd's and a final reporting with an UI loading binary from the datamodel.

- Marcus