Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
americanetsMD
Contributor III
Contributor III

pyramid pattern using nested loops

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. 😞

americanetsMD_0-1697552173814.png

 

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

Labels (1)
1 Solution

Accepted Solutions
marcus_sommer

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. 

View solution in original post

2 Replies
marcus_sommer

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
Contributor III
Contributor III
Author

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. 🙂