pyramid pattern using nested loops

[americanetsMD]

Hello Wveryone,

I would really appreciate if someone could help me out solving the following problem.

Consider the table:

Year	Konto	HSLVT	HSL01	HSL02	HSL03	HSL04	HSL05	HSL06	HSL07	HSL08	HSL09
2023	39000	248.900,00	9233.83	-91132.67	-55.958,20	-13.432,00	84.089,00	-48.625,00	-31.077,00	16.231,00	-29.584,00

My Task is to calculate all the values for Konto the total in each month in year. In this case it should look like:

Jan 23	Feb 23	Mrz 23	Apr 23	Mai 23	Jun 23	Jul 23	Aug 23	Sep 23
258.133,83	167.001,16	111.042,96	97.610,96	181.699,96	133.074,96	101.997,96	118.228,96	88.644,96
HSLVT +	HSLVT +	HSLVT +	HSLVT +	HSLVT +	HSLVT +	HSLVT +	HSLVT +	HSLVT +
HSL1	HSL01 +	HSL01 +	HSL01 +	HSL01 +	HSL01 +	HSL01 +	HSL01 +	HSL01 +
	HSL2 +	HSL02 +	HSL02 +	HSL02 +	HSL02 +	HSL02 +	HSL02 +	HSL02 +
		HSL03	HSL03 +	HSL03 +	HSL03 +	HSL03 +	HSL03 +	HSL03 +
			HSL04	HSL04 +	HSL04 +	HSL04 +	HSL04 +	HSL04 +
				HSL05	HSL05 +	HSL05 +	HSL05 +	HSL05 +
					HSL06	HSL06 +	HSL06 +	HSL06 +
						HSL07	HSL07 +	HSL07 +
							HSL08	HSL08 +
								HSL09

I try to do it through a FOR loop and i get the needed Datum, but i don't get the needed Sum..

i believe i have to use something similar as a pyramid pattern using nested loops in Java, but it doesn't work. 😞

Currently i use this FOR loop:

For i = 1 to 12 step 1
If not i = 12 then
let vField = 'HSL' & Num($(i), '00');
Trace $(vField);
Concatenate [ResultTab]:
Load
Key,
ClientNr,
Jahr,
Currency,
Konto,
Sum($(vField))as Value,
MonthEnd(MakeDate(Jahr, $(i))) as Datum
Resident TemporaryTab
Where not(IsNull(ClientNr))
and Konto = '0003910000'
Group By Key, ClientNr, Jahr, Currency, Konto
;

EndIf

Next i

[marcus_sommer · Accepted solution]

I'm not sure if I understand your aim right but I think your low-level programming approach isn't really suitable within Qlik because there are already various high-level features implemented which do the job and are simplifying a lot of work.

In your case you may use The Crosstable Load - Qlik Community - 1468083 to transform your crosstable into a stream-data structure. An aggregating isn't mandatory needed and if an accumulation is wanted within the data-model the usage of interrecord-functions like previous() or peek() within a sorted resident-load is probably a more suitable approach.

Because of the fact that all records contain a date could these data be associated with a master-calendar whose fields might be used as dimensions/selections and/or set analysis conditions within the UI. And with above() and similar functions you could do there accumulations, too. Further possibilities are to create various flags like YTD or rolling periods in the master-calendar and/or applying any The As-Of Table - Qlik Community - 1466130 to get an easy handling by overlapping dimension-values. 

[americanetsMD]

Hi Marcus,

thank you very much for the tips. These were new for me. i have applied the As-Of-Table and it worked out nicely for my task. 🙂