Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Use for next in load from excel

Hello,

Here is my first question. Until now I always found the solution on this community. But I did not find an answer for my problem.

I load from excel for each item nr : Name, Forecast

Forecast:

LOAD

  Item Nr,

          Name,

  forecast

FROM

I:\DataSourceExcel\ProductionTargets2012.xlsx

(ooxml, embedded labels, table is Reeks);

Now I want to create an autogenerate table that will fill up a table in which I want to have for each Item Nr a weekly forecast.

So I want a table like this :

ItemNrWeekWeekForecast
12012/01Forecast of Item Nr 1 divided by 52
12012/02Forecast of Item Nr 1 divided by 52
12012/03Forecast of Item Nr 1 divided by 52
1...Forecast of Item Nr 1 divided by 52
12012/52Forecast of Item Nr 1 divided by 52
22012/01Forecast of Item Nr 2 divided by 52
22012/02Forecast of Item Nr 2 divided by 52
2...Forecast of Item Nr 2 divided by 52
22012/52Forecast of Item Nr 2 divided by 52
.........

I

Can anyone help me ? Have been searching for days to find a solution but without luck...

Greetings

Dirk

1 Solution

Accepted Solutions
Not applicable

Use for next in load from excel

I found the solution my self.

This is how I solved it, I'm posting it becaust it could be helpfull for others maybe...

for x = 2011 to 2012

          for i = 1 to 52

                         if i < 10 then

                                        let vnummer = chr(39) & $(x) & '/0' & $(i) & chr(39);

                         else

                                        let vnummer = chr(39) & $(x) & '/' & $(i) & chr(39);

                         ENDIF

                         LOAD

                                        Name,

                                        text($(vnummer)) as WeekForecastWeek,

                                        (S2012 * (PerMaat/100)) / 52 as WeekForecast

       FROM

                         I:\DataSourceExcel\ProductionTargets2012.xlsx

                         (ooxml, embedded labels, table is Reeks);

          next i;

next x;

Thanks to everybody who tried to help me !

Greetings Dirk

4 Replies
IAMDV
Honored Contributor II

Re: Use for next in load from excel

Hi Dirk,

Please can you post the sample data? I can work an example for you.

Scramble the data if it is sensitive.

Thanks - DV

Not applicable

Use for next in load from excel

Hi D V,

I do not knwo how I can post the excel with the QV to this Discussion.

Sample Data in Excel is quite simple. Looks like this :

excelsample.jpg

Starting from this forecast, which is for a complete year, I want a table that holds for each item a value for each week of the year.

With this table I can show in a graph how stock will lower each week.

Later on, I can add incoming quantities in the same chart, so this chart will show stock level forecast.

Thanks for helping me.

Greetings,

Dirk

michielvandegoo
Valued Contributor

Use for next in load from excel

Dirk,

If this data is in Excel as the screen shot than you might want to try this:

load the table

link this table to your fact table

that might also solve possible 'level of detail' issues (transaction data by day and forecast data by week)

(if the table has a week number).

Hope this helps

regars

Michiel

Not applicable

Use for next in load from excel

I found the solution my self.

This is how I solved it, I'm posting it becaust it could be helpfull for others maybe...

for x = 2011 to 2012

          for i = 1 to 52

                         if i < 10 then

                                        let vnummer = chr(39) & $(x) & '/0' & $(i) & chr(39);

                         else

                                        let vnummer = chr(39) & $(x) & '/' & $(i) & chr(39);

                         ENDIF

                         LOAD

                                        Name,

                                        text($(vnummer)) as WeekForecastWeek,

                                        (S2012 * (PerMaat/100)) / 52 as WeekForecast

       FROM

                         I:\DataSourceExcel\ProductionTargets2012.xlsx

                         (ooxml, embedded labels, table is Reeks);

          next i;

next x;

Thanks to everybody who tried to help me !

Greetings Dirk

Community Browser