selectively load columns from table

[#1]

Hi Experts,

I'm new to qlikview and I'm not sure how to achieve the following requirement. Given a table with 24 months revenue columns such as [Jan PY Rev] to [Dec CY Rev]. I want to load the columns based on this requirement:

1) Load column = current month - 2; for example, if current month is Jul, load column should be May or [May CY Rev].

2) load column = current month - 3; if current month is Jul, load column = [Apr CY Rev].

3) load column = current month of least year;  if current is Jul, load Jul of last year, the column is [Jul PY Rev].

I have tried using expression such as nested if(current month=1, [Jan CY Rev], if (current month=2, [Feb CY Rev]...etc)) as current_month_year. This works fine for the current month and last year of same month. However, it does not work for two and three months ago scenarios where let's say current month is January [Jan CY Rev], the 2 and 3 previous months are [Nov PY Rev] and [Oct PY Rev] because at the beginning of the current month, the previous months rolls back to previous month; that is why it does not work. Then I started trying to load the fields and rename them to numeric so I can use the expression to dynamically load the columns corresponding to the numeric number. For instance, load [Jan PY Rev] as Rev1 all the way to [Dec CY Rev] as Rev24 and use if (current month=1, Rev1 or substitute 1 with a variable, qlikview says Rev1 not found. I don't know any other way to make this to work. Any sample is appreciated. Thanks. Quoc

[Anonymous]

Hi,

See attachment,

TabA:

CrossTable(Label, Value,1)

LOAD * INLINE [

    Dim, Jan PY Rev, Feb PY Rev, Mar PY Rev, Apr PY Rev, May PY Rev, Jun PY Rev, Jul PY Rev, Aug PY Rev, Sep PY Rev, Oct PY Rev, Nov PY Rev, Dec PY Rev, Jan CY Rev, Feb CY Rev, Mar CY Rev, Apr CY Rev, May CY Rev, Jun CY Rev, Jul CY Rev, Aug CY Rev

    A, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100

    B, 150, 150, 150, 150, 150, 150, 150, 150, 150, 150, 150, 150, 150, 150, 150, 150, 150, 150, 150, 150

];

TabB:

LOAD

  Dim,

  Date,

  Rev_Flag,

  Revenue

Where Match(Date, MonthStart(AddMonths(Today(), -2)), MonthStart(AddMonths(Today(), -3)), MonthStart(AddYears(Today(), -1)));

LOAD

  Dim,

  Date(Date#(If(Trim(SubField(Label, ' ', 2))='PY', Year(Today())-1, If(Trim(SubField(Label, ' ', 2))='CY', Year(Today())))&Left(Label, 3), 'YYYYMMM')) as Date,

  Trim(SubField(Label, ' ', 2)) as Rev_Flag,

  Value as Revenue

Resident TabA;

DROP Table TabA;

[#3]

Hi Satyadev,

Thank you for your answer. I will try to mimic your sample to see if it works for me. From the look of the example, I see you use date column which in my table it does not have any. The column names are the dates themselves and the values are contained within. Additionally I do see you use two loads in one table, is that invented? I'm not familiar with different loads to understand this and the user of cross table and match function. Where can I get more information on these to learn more about them? Thanks again for your big help.

[Anonymous]

Hi Quoc,

Let me explain you what actually I am doing here.

First I am loading the table with crosstable load and converting all column headers [Jan PY Rev], [Feb PY Rev] etc to underline data in "Label" column and all values in "Value" column.

In second load which is "resident load" in qlikview, I am creating "Date" field with the help of label column and some qlikview functions and renaming "value" column as "Revenue" etc

In third load which is "preceding load" in qlikview, I am using Date column in where clause to load only current month -2, current month -3 and current month of last year by using some qlikview functions.

I would suggest to have a look on QlikView manuals, help, qlik community, you tube video to learn qlikview. There are lots of qlikview blogs around web. You can even check them.

Hope it helps you.

[#5]

Hi Satyadev,

I understand your sample and I can be able to apply it to my table.  I do see that I am able to transform columns to rows and pull up the dates (Current, Current - 2, Current - 3, and Current of Last Yeart).  However, I would like to use these dates as Headers and the Revenues in the cells corresponding to the Column Header.  How can I achieve that?  I do not have a way to attach the screenshots I made to show you what I want to achieve.  Thanks! Quoc.

[Anonymous]

Hi Quoc,

Best way to achieve this to include Label field into load statement and rename it as Header and use this field in Pivot table. See attachment.

TabB:

LOAD

    .........

    Header

Where Match(Date, MonthStart(AddMonths(Today(), -2)), MonthStart(AddMonths(Today(), -3)), MonthStart(AddYears(Today(), -1)));

LOAD

    .........

    Label as Header

Resident TabA;

You can even add calculated dimension with below script but this will consume lots of memory during chart calculation and you also have to add extra script for sorting the header in order.

=Month(Date)&' '&Rev_Flag&' Rev'

[#7]

Thanks Satyadev. I will take a look and try out your sample tomorrow. Meanwhile, I have another question. Since transforming the columns to rows takes a long time as I used your previous sample in my actual application, if I are to load the data from the database to excel in an already transformed to rows, how would I be able to achieve loading the same months as previously described requirement? My goal is to show those months with column headers such as Jul 2014 Rev and revenue in the cell. I also want to reduce the transformation during load as possible. Currently I use your sample and I see the original Excel with 58k rows, during cross table loading it loads 1.5 m rows which is extremely slow. If there's is a way too achieve the same requirement in an already transformed data, I just need to create the ui and just use the data. What do you think? Thanks!  Quoc

Ok - I finally tried out the sample.  I think in my previous post I did not state clearly what I want to achieve.  I attach the screenshots this time so you can see what I want to do.  Since leaving the columns with dates as headers and revenue in the cells is hard to selectively select the column to display in chart or any table, using cross table transforms that to rows and sorts out the wanted dates.  This is great but I can't still display.

Given table:

Dim | Jan PY Rev | Feb PY Rev | ... | Jul PY Rev  | ... | Apr CY Rev | May CY Rev | Jun CY Rev | Jul CY Rev | ... | Nov CY Rev | Dec CY Rev |

A    | 100             | 150             | ... | 200             | ... | 100             | 50              | 200             |  300          | ... | 50               | 200             |

A    | 150             | 150             | ... | 200             | ... | 150             | 150            | 300             | 300            | ... | 50               | 200            |

I want to display:

Dim | Jul PY Rev | Apr CY Rev | May CY Rev | Jul CY Rev |

A    | 200            | 100             | 50               | 200            |

B    | 200            | 150             | 150             | 300            |

That shows Current month (July) current year.  -2 months (May) current year. -3 month (Apr) current year, and Current month (Jul) last year.

Hopefully I am clear this time and looking for a way to achieve this with not too much of columns rows transformation.  If it can be in rows in the source data, I can try transforming that before loading too.  Thanks! Quoc.

[Anonymous]

Hi Quoc,

I do understand your concern. Definitely, 58k rows with 24 columns in excel will impact loading process with cross table join. I was thinking and came up with another logic, Why can't you load only those 4 columns in initial loading, for example below,

create 4 variables,

LET vCM_3=Month(AddMonths(Today(), -3)); //Current Month -3

LET vCM_2=Month(AddMonths(Today(), -2)); //Current Month -2

LET vCM=Month(AddMonths(Today(), 0)); //Current Month

LET vPY_CM=Month(AddYears(Today(), -1)); //Last Year Current Month

TabA:

LOAD Dim,

  "$(vCM_3) CY Rev" as "$(vCM_3) CY Rev",

  "$(vCM_2) CY Rev" as "$(vCM_2) CY Rev",

  "$(vCM) CY Rev" as "$(vCM) CY Rev",

  "$(vPY_CM) PY Rev" as "$(vPY_CM) PY Rev"

FROM

DATA.xlsx

(ooxml, embedded labels, table is Sheet1);

Now its easy for you to do either cross table joining which will take lesser time compare to full load or keep the table as it is and use those columns in your straight table.

Hope this make sense to you.

[#9]

Hi Satyadev,

I think this solution reduces the load; however, it will not work for cases when the current month is Jan and Feb because the current month minus 2 and 3 will put in the last year with PY.  For instance, if current month is Jan (Jan CY Rev), minus 2 and 3 will be Nov PY Rev and Oct PY Rev.  In your example, you only use CY.  Remember there are 12 months in PY and 12 months in CY.  That is why it fails the case where the current month is at the beginning of the year.  I have thought about this solution initially where I don't think I can safely cycle through the months without problems.  Thank you for your help.  I am looking at another solution where the data was already transformed to rows and use that data to find the months I want to load.  Basically I do not need to transform from columns to rows using crosstable because the data from the database will have to be in rows ready for use.  I just need to know how to use that in that way to achieve what I am looking for to do.  Thanks! Quoc

[Anonymous]

Hi Quoc,

Except crosstable join, I don't think so there is any other option to transform columns to rows in qlikview. I would suggest to go with QVD approach. First transform all data into row and store it in QVD and then, in the application, load that QVD with specific logic to grab only current month -3, current month -2, current month and last year same month. Thanks