Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

rank_nandha
Contributor II

For Loop in qlikview

Hi,

I
am new to software development and Qlikview. I have a pivot table in my dashboard;
I am using this table for multiple selections. For example I have prompt selection
in the name of yearly, half yearly, Quarterly and monthly. If I select the
monthly prompt a multi box will appear adjacent to the prompt. Under the
monthly box list of month will be displayed, if I select the corresponding
month by say April the pivot table should show me the April data; if I select
the Dec month pivot should show Dec data. Like this it’s applicable for
quarterly (Q1=Jan+Feb+Mar) and half yearly (H1=Jan+Feb+Mar+Apr+May+Jun).

I
want to use only one pivot table for this scenario. Now I am using multiple pivot
tables (copy pasting it) by created a variable and call that variable under conditional
layout properties.

Is
there a solution for this where I can use only one pivot table for the entire
scenario?

Thanks in advance

Regards,

Nandha

1 Solution

Accepted Solutions
rank_nandha
Contributor II

Re: For Loop in qlikview

I have changed the funda in DB itself. This works fine now.

I have changed the column into row and stored as temp table in DB and calling the values from that temp table.

5 Replies
Not applicable

Re: For Loop in qlikview

Nandha,

Si entendi bien, deseas que tu tabla Pivot aparezca con los datos del mes seleccionado? Es correcto?

Estas utilizando un "Master Calendar" en tu script?

La solución esta en crear un calendario principal, en el cual puedes configurar todas tus combinaciones de fechas, intervalos, ytd, etc.... y tenerlos como campo seleccionable. Así tus tablas traeran los datos por las fechas, mes, ytd, Q, Y, etc como lo deseas.

Patrick

rank_nandha
Contributor II

Re: For Loop in qlikview

Dear Patrick,

I'm not using the calendar script I'm using the Inline function for this prompt selection. Now i am using 22 pivot table to get the table data. I want this data to be displayed in single table and change accordingly when i select the prompt. Pls find the script that I used to edit script i used for this below

Month:


Load * Inline [

Es_Month, Eg_Month,Half,Half_Num, month_num,Quarter,Quarter_Num

Mes1, Ene,H1,17, 1,Q1,1

Mes2, Feb,H1,17, 2,Q1,1

Mes3, Mar,H1,17, 3,Q1,1

Mes4, Abr,H1,17, 4,Q2,2

Mes5, May,H1,17, 5,Q2,2

Mes6, Jun,H1,17, 6,Q2,2

Mes7, Jul,H2,18, 7,Q3,3

Mes8, Ago,H2,18, 8,Q3,3

Mes9, Spe,H2,18, 9,Q3,3

Mes10, Oct,H2,18, 10,Q4,4

Mes11, Nov,H2,18, 11,Q4,4

Mes12, Dic,H2,18, 12,Q4,4

];


Menus:

LOAD * INLINE [

    Menu, Menu_Num

    Anual, 1

    Semestral, 2

    Trimestral, 3

    Mensual, 4

];

Not applicable

Re: For Loop in qlikview

Nandha,

Well, as far as I can understan, you have a or various tables with your data (not the inline table Month) with your values and also one or various dates fieds. Those fields must be used to create a calendar or diferent fields with your dates type (month, year, Q, etc.)

For example: You have a table with your sales per days like this

DateArticle
CantityUnit PriceTotal
01-10-2012Pen525125
05-10-2012Paper21020
15-11-2012Light10550
18-11-2012Pen825200
01-12-2012Paper61060

Now, in your script when you load your data, you can create new fields based on your original data table, in this case the Date fields.

For example (read all the date format in Help menu)

month(Date) as Month,

Year(Date) as Year,

'Q' & ceil(Date / 3) AS C_Quarter,

          'Q' & ceil(Date/ 3) & '-'& Y AS C_QuarterYear,

          week(Date) AS C_Week,

          weekday(Date + 2) AS C_Weekday,

          weekName(Date AS C_Semanas,

          day(Date AS C_Day,

          date(Date,'DD/MM/YY') AS C_DateDDMMYY,

          QuarterName("Date") as C_Trimestres,

          Monthname("Date") as C_Mes,

          Month(today()) as C_MesActual,

          inmonthtodate("Date", $(vToday), 0) * -1 AS C_MTD,

So, doing that you can have a List Box and select the month, week, Q, etc as you wich and your Pivot table will display the data for such month, week etc... If you wich to change the display title of the dimensions, only put the field in the subject and it will change also.

On the other way, you will maybe need a master calendar, but for that, I suggest you to look around in the forum and documentation for calendar subject.

Hope this help.

rank_nandha
Contributor II

Re: For Loop in qlikview

I have changed the funda in DB itself. This works fine now.

rank_nandha
Contributor II

Re: For Loop in qlikview

I have changed the funda in DB itself. This works fine now.

I have changed the column into row and stored as temp table in DB and calling the values from that temp table.

Community Browser