Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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

Re: Looping (or something else perhaps)?

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

2 Replies
Not applicable

Re: Looping (or something else perhaps)?

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

Re: Looping (or something else perhaps)?

Thanks Sean

I guessed there was a better less verbose method.

Oli

Community Browser