Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

ravishinge
Contributor

For loop increment by every month

hello guys ,

I need Value 12 for 1st four months and then another set of value  from 2nd months onwards and so on as shown below .

I need to autogenerate value 12 four times every coming month and so on till 20 months .

Months12345678910
112
21212
3121212
412121212
5 121212
6 1212
7 12
8 12
9 12
10 12
12

Here is my script it does not give me desired results . Could u plz help me with solution .  When i use Value and Month in the chart i will get value= 12 for 20 months . But i need something as shown in the matrix above .

FOR i = 1 TO 20

Let Duration=i;

[XYZ]:

LOAD

           '12' as Value,

           'Month' & $(Duration) as Month

          AutoGenerate 1;

           

NEXT i

1 Solution

Accepted Solutions
YoussefBelloum
Esteemed Contributor

Re: For loop increment by every month

here is the new code:

[DataNew1]:

LOAD * Inline [

"Impact month"

12

];

let vImpact_Month= Peek('Impact month',0,DataNew1);

TRACE $(vImpact_Month);

for i=1 to 10

for x=1 to 4

if $(i) =1 then

test:

load

$(i) as Month,

$(x) as Month2,

$(vImpact_Month) as Value

AutoGenerate 1;

ELSE

test:

load

$(i) as Month,

$(x)+($(i)-1) as Month2,

$(vImpact_Month) as Value

AutoGenerate 1;

ENDIF

NEXT x;

NEXT i;

14 Replies
YoussefBelloum
Esteemed Contributor

Re: For loop increment by every month

Hi,

Try this:

for i=1 to 10

     for x=1 to 4

     if $(i) =1 then

          test:

          load

          $(i) as Month,

          $(x) as Month2,

          12 as Value

          AutoGenerate 1;

     ELSE

          test:

          load

          $(i) as Month,

          $(x)+($(i)-1) as Month2,

          12 as Value

          AutoGenerate 1;

     ENDIF

     NEXT x;

NEXT i;



==> create a pivot table with Month and Month1 as dimensions and sum(value) as expression

ravishinge
Contributor

Re: For loop increment by every month

Thank you very much ! It worked like a charm

i want to do a resident load . But i am not able to put it .

Kindly let me know where do I put

  Resident [DataNew1];      in the script .

Thanks,

Ravi

YoussefBelloum
Esteemed Contributor

Re: For loop increment by every month

Example for RESIDENT:

TABLE1:

load *

from...

TABLE2:

load *

RESIDENT TABLE1;

==> the only condition is to load a table already loaded on the model.

ravishinge
Contributor

Re: For loop increment by every month

Thanks for reply !

Yes i have a loaded table which is [DataNew1]

Now i want to Resident this in the script what you suggested but I am not able to put   Resident [DataNew1];  in the script .

Where should i write Resident [DataNew1];  in the above script ? I mean after End if or after Autogenerate or ....?

YoussefBelloum
Esteemed Contributor

Re: For loop increment by every month

I used Autogenerate because I started with your script..

but you can't use resident with autogenerate.

you must know: if you want to generate the MONTHS/Value table or the table already exists on the model.

the best you can do is to attach your App if there is no private data.

ravishinge
Contributor

Re: For loop increment by every month

Hello ,

[DataNew1]:

LOAD

    "Product Line",

    "Impact month",

FROM [lib://AttachedFiles/Complexity_Mix.xlsx]

(ooxml, embedded labels, header is 1 lines, table is Model);

For i=1 to 10

     For x=1 to 4

    

     if $(i) =1 then

          test:

          load

          $(i) as Month,

          $(x) as Month2,

          "Impact month"    I need this "Impact month"  field from [DataNew1]:  (Impact month has value 12)

//           12 as Value

          AutoGenerate 1;

   

     ELSE

          test:

          load

          $(i) as Month,

          $(x)+($(i)-1) as Month2,

             "Impact month"    I need this "Impact month"  field from [DataNew1]:  (Impact month has value 12)

//           12 as Value

 

          AutoGenerate 1;

 

     ENDIF

     NEXT x;

NEXT i;

So how do I get this "Impact month"  field in the loop ?

Highlighted
YoussefBelloum
Esteemed Contributor

Re: For loop increment by every month

I should modify the script to add another column with an autogenerate..

can you show what is the expected result with this new column ?

because I'm already generating 2 months columns

ravishinge
Contributor

Re: For loop increment by every month

Ok !

If you see initially what we did in the script is

      '' 12 as Value ''


This value 12 is actually in the field named as [Impact Month ]

So rather than mentioning 12 as Value  , I wanted to  include Field [Impact Month ] in the script .

Bcoz now it has value 12 , 2moro it may have some other value so as to make it more dynamic .

And [Impact Month ] is in the 1st Table named as [DataNew1]:   

So was trying to use Resident , But i am not able to

Thnx

YoussefBelloum
Esteemed Contributor

Re: For loop increment by every month

here is the new code:

[DataNew1]:

LOAD * Inline [

"Impact month"

12

];

let vImpact_Month= Peek('Impact month',0,DataNew1);

TRACE $(vImpact_Month);

for i=1 to 10

for x=1 to 4

if $(i) =1 then

test:

load

$(i) as Month,

$(x) as Month2,

$(vImpact_Month) as Value

AutoGenerate 1;

ELSE

test:

load

$(i) as Month,

$(x)+($(i)-1) as Month2,

$(vImpact_Month) as Value

AutoGenerate 1;

ENDIF

NEXT x;

NEXT i;