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: 
Not applicable

Looping (or something else perhaps)?

Hi

On our database we have a sales achieve file which holds sales by month for the last 3 years.

I have written a LOAD statement that pick these fields out and concatenates them into a single table eg.

SalesHistoryByAcc:

LOAD

    SAAACN & '-' & SAABRN as %Acc,

    SAAS01 as [Sales value],

    $(vYear) as year,

    dual('Jan',1) as month

;

SQL

    SELECT SAAACN, SAABRN, SAAS01 FROM ....SAHACCPF;

LOAD

    SAAACN & '-' & SAABRN as %Acc,

    SAAS02 as [Sales value],

    $(vYear) as year,

    dual('Feb',2) as month

;

SQL

    SELECT SAAACN, SAABRN, SAAS02 FROM ....SAHACCPF;

LOAD

    SAAACN & '-' & SAABRN as %Acc,

    SAAS03 as [Sales value],

    $(vYear) as year,

    dual('Mar',3) as month

;

SQL

    SELECT SAAACN, SAABRN, SAAS03 FROM ....SAHACCPF;

...etc etc...

I know you can use a For..Next loop in the script, but can I use it in a Field name ie "SAAS0" & n

Thanks

Oli

1 Solution

Accepted Solutions
Not applicable
Author

Hi Oli,

Yes, you can use it with Dollar Sign Expansion.

i.e

For i=1 to 9

SalesHistoryByAcc:

LOAD

    SAAACN & '-' & SAABRN as %Acc,

    SAAS0$(i) as [Sales value],

    $(vYear) as year,

    dual('Jan',1) as month

;

SQL

    SELECT SAAACN, SAABRN, SAAS0$(i) FROM ....SAHACCPF;

Next

or

For Each i in '01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '11', '12'

SalesHistoryByAcc:

LOAD

    SAAACN & '-' & SAABRN as %Acc,

    SAAS$(i) as [Sales value],

    $(vYear) as year,

    dual('Jan',1) as month

;

SQL

    SELECT SAAACN, SAABRN, SAAS$(i) FROM ....SAHACCPF;

Next i

This will go through SAAS01, SAAS02, SAAS03 ... SAAS12.

You may need to work on the year and month logic to make it generic.

For Each vYear in  ....

     For Each i in '01' ... '12'

          SalesHistoryByAcc:

          LOAD

              SAAACN & '-' & SAABRN as %Acc,

              SAAS$(i) as [Sales value],

              $(vYear) as year,

              //dual('Jan',1) as month

               if(i = '01',

                    dual('Jan',1),

                    if(i ='02',

                         dual('Feb',2)

                    ...

                    )) as month,

          Dual(Date('$(i)/01/$(vYear)','MMM'), num($(i))) as month2

          ;

          SQL

              SELECT SAAACN, SAABRN, SAAS$(i) FROM ....SAHACCPF;

     Next i

Next vYear

Hopefully this is what you needed.

Sean

View solution in original post

2 Replies
Not applicable
Author

Hi Oli,

Yes, you can use it with Dollar Sign Expansion.

i.e

For i=1 to 9

SalesHistoryByAcc:

LOAD

    SAAACN & '-' & SAABRN as %Acc,

    SAAS0$(i) as [Sales value],

    $(vYear) as year,

    dual('Jan',1) as month

;

SQL

    SELECT SAAACN, SAABRN, SAAS0$(i) FROM ....SAHACCPF;

Next

or

For Each i in '01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '11', '12'

SalesHistoryByAcc:

LOAD

    SAAACN & '-' & SAABRN as %Acc,

    SAAS$(i) as [Sales value],

    $(vYear) as year,

    dual('Jan',1) as month

;

SQL

    SELECT SAAACN, SAABRN, SAAS$(i) FROM ....SAHACCPF;

Next i

This will go through SAAS01, SAAS02, SAAS03 ... SAAS12.

You may need to work on the year and month logic to make it generic.

For Each vYear in  ....

     For Each i in '01' ... '12'

          SalesHistoryByAcc:

          LOAD

              SAAACN & '-' & SAABRN as %Acc,

              SAAS$(i) as [Sales value],

              $(vYear) as year,

              //dual('Jan',1) as month

               if(i = '01',

                    dual('Jan',1),

                    if(i ='02',

                         dual('Feb',2)

                    ...

                    )) as month,

          Dual(Date('$(i)/01/$(vYear)','MMM'), num($(i))) as month2

          ;

          SQL

              SELECT SAAACN, SAABRN, SAAS$(i) FROM ....SAHACCPF;

     Next i

Next vYear

Hopefully this is what you needed.

Sean

Not applicable
Author

Thanks Sean

I guessed there was a better less verbose method.

Oli