topic Re: Looping (or something else perhaps)? in QlikView

Looping (or something else perhaps)?

Mon, 06 Jan 2014 14:24:16 GMT

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

Re: Looping (or something else perhaps)?

Mon, 06 Jan 2014 14:47:33 GMT

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;

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

Re: Looping (or something else perhaps)?

Mon, 06 Jan 2014 15:16:11 GMT

Thanks Sean

I guessed there was a better less verbose method.

Oli