Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
Champion
Champion

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;

View solution in original post

14 Replies
YoussefBelloum
Champion
Champion

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

Anonymous
Not applicable
Author

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
Champion
Champion

Example for RESIDENT:

TABLE1:

load *

from...

TABLE2:

load *

RESIDENT TABLE1;

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

Anonymous
Not applicable
Author

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
Champion
Champion

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.

Anonymous
Not applicable
Author

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 ?

YoussefBelloum
Champion
Champion

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

Anonymous
Not applicable
Author

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
Champion
Champion

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;