topic Re: Dymanic loop in QlikView

Dymanic loop

Wed, 03 Feb 2016 07:06:14 GMT

Hello All,

I have table "dbo.ACTION" which is found in my database "HN_PRODUCTION"

However, we have just created a new database "HN_BACKUP" but now the table are as follows:

>HN_BACKUP

>Tables

>dbo.ACTION_2015_06

>dbo.ACTION_2015_07

>dbo.ACTION_2015_08

>dbo.ACTION_2015_09

>dbo.ACTION_2015_10

>dbo.ACTION_2015_11

>dbo.ACTION_2015_12

>dbo.ACTION_2016_01

The issue is that I have to load only the last three months.

To resume I have to load the below tables:

>dbo.ACTION which is HN_PRODUCTION

>dbo.ACTION_2015_11

>dbo.ACTION_2015_12

>dbo.ACTION_2016_01

I managed to load the table using the script below:

Action:

LOAD ActionLocalTime,

Date(Floor(ActionLocalTime)) AS Date,

AgentId as FirstAgent,

Campaign as CampaignId,

Duration as DurationPre,

State

SQL SELECT *

FROM "HN_PRODUCTION".dbo.ACTION where NextState='96';

FOR Each a in '2015_12','2015_11','2015_10','2016_01'

Action:

LOAD ActionLocalTime,

Date(Floor(ActionLocalTime)) AS Date,

AgentId as FirstAgent,

Campaign as CampaignId,

Duration as DurationPre,

State

SQL SELECT *

FROM "HN_BACKUP".dbo.ACTION_$(b) where NextState='96';

HOWEVER, for next month a new table will be created in "HN_BACKUP" as "dbo.ACTION_2016_02" and I will need to load the data of the tables below:

>dbo.ACTION which is HN_PRODUCTION

>dbo.ACTION_2015_12

>dbo.ACTION_2016_01

>dbo.ACTION_2016_02

I have around 35 qvw using these table.

Any idea on how to I can do it.

Kind Regards,

Hasvine

Re: Dymanic loop

mightyqlikers — Wed, 03 Feb 2016 07:17:37 GMT

Hi,

try like below

Action:

LOAD ActionLocalTime,

Date(Floor(ActionLocalTime)) AS Date,

AgentId as FirstAgent,

Campaign as CampaignId,

Duration as DurationPre,

State

SQL SELECT *

FROM "HN_PRODUCTION".dbo.ACTION where NextState='96';

for each fle in '2015_12','2015_11','2015_10','2016_01'

load

* ;

sql

select * from dbo.ACTION_$(fle);

next $(fle)

Regards
$@M

Re: Dymanic loop

Wed, 03 Feb 2016 07:21:12 GMT

Hi Sam,

Can I make te below dynamic so that it calculate the last 3 months?

for each fle in '2015_12','2015_11','2015_10','2016_01'

Thanks,

Hasvine

Re: Dymanic loop

tresB — Wed, 03 Feb 2016 07:30:56 GMT

May be like:

for each fle in

Date(AddMonths(Today(),-3), 'YYYY_MM'),

Date(AddMonths(Today(),-2), 'YYYY_MM'),

Date(AddMonths(Today(),-1), 'YYYY_MM')

Re: Dymanic loop

Wed, 03 Feb 2016 07:40:52 GMT

Hello Tresesco,

I works.

Thanks,

Hasvine