Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Dymanic loop

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

1 Solution

Accepted Solutions
MVP
MVP

Re: Dymanic loop

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')

4 Replies
mightyqlikers
Contributor III

Re: Dymanic loop

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

Not applicable

Re: Dymanic loop

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

MVP
MVP

Re: Dymanic loop

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')

Not applicable

Re: Dymanic loop

Hello Tresesco,

I works.

Thanks,

Hasvine

Community Browser