Report building

[priyarane]

Hi Experts, Can some one please help me to build the report as attached. Excel is the source file and as per the screen shot, I required the report. 1, 2 3 we can avoid in the first column.

At least give me proper format data to get my requirement.

Thank you.

[petter · Accepted solution]

Here you have a sample app with the data read from your SOURCE.XLSX and unpivoted into a data model in QlikView with the help of CROSSTABLE LOAD. The UI consists of two sheets with the report displayed in the first as PIVOT TABLE and in the second as a STRAIGHT TABLE - quite true to the original.

Find the attachment with a working QlikView application.

[priyarane]

This is really helpful, Petter. Thank you

[priyarane]

Hi Petter,

If possible could you please explaing how you did this, I know you have used cross table. But still confused at script part.

[petter]

Hi Priya,

Yes the CROSS TABLE is used to unpivot the periodic numbers that essentially should be kept in the same field/column with only variation on a time between in the source-columns. The script does a CROSS TABLE transform to merge all these columns into one single field in QlikView. You then get much fewer columns but a lot more rows. This is almost always the best way of representing the data in the data model of a QlikView application. For data manipulation purposes and flexibility this is quite optimal. This practice is not confined to QlikView at all but considered best practice in for most analytical/BI purposes.

The extra table I called RowHeaders is only to keep the common and repeating information from the datasheet part. This is not absolutely neccessary but is more efficient and cleaner. It has to be connected to the datasheet so the headers can be used whenever you want to display/use the data in a chart.

In QlikView UI (charts mostly) it is straightforward to get the data out into columns again - both in PIVOT TABLE and also STRAIGHT TABLE (although more statically).

Hope this explains the things you asked about...

- Petter

[priyarane]

Thanks Petter,

But when I pull the data from Source then I will get like below:

LOAD Item,

     F2,

     date,

     [PQ 1],

     [PQ 2],

     [PQ 3],

     [PQ 4],

     [PQ 5],

     [PQ 6],

     [PQ 7],

     [PQ 8],

     [PQ 9]

FROM

but in your model got like this:

[DataSheet]:

CrossTable(AP, Data, 1)

LOAD

  Item*10 AS Item,

     date AS Actual,

     [PQ 1],

     [PQ 2],

     [PQ 3],

     [PQ 4],

     [PQ 5],

     [PQ 6],

     [PQ 7],

     [PQ 8],

     [PQ 9]

FROM

so how did you move out the F2, what is AP

[priyarane]

Because, I have many reports to build with same kind of Formatted data.

If I understood then I can quickly do these reports.

hope you understand

[petter]

AP is the name I give for the extra field that QlikView needs to create when it does the CROSSTABLE LOAD. It needs a field to actually store the column headers in so the numbers still can be connected to a specific PQ (Projected Quarter or the Actual As Of Date).... so I just selected the name AP as short for Actual or Projected ... and this field will get the values 'PQ 1', 'PQ 2' .... 'PQ 9' and 'Actual' as the numbers is moved from 10 columns into 1 field in QlikView.

Item an Date is headers that QlikView pick up from the SOURCE.XLSX ... but I did make sure that one row in the top of the spreadsheet was a header... and that it has embedded labels ... that is why my LOAD statement give Item instead of F2 ... have a look at what i mention after LOAD .... FROM SOURCE.XLSX (.....) between the paranthesis most likely you will find a difference between your statment and mine.

[petter]

Yes I understand that... It is exactly why I wanted to show you an example that is extensible so it is easier to handle more reports and many reports... so this approach is indeed extensible. I will have a look at your attachment and see how it works - later today.

[priyarane]

Thanks Petter,

But how did you get this,

[RowHeaders]:

LOAD
RecNo() AS RowNo,
@1 * 10 AS Item,
If( IsNum(@1) , Peek(ItemTxt) , @1) AS ItemTxt,
@2 AS [Item Text]
FROM
Souce.xlsx
(ooxml, no labels, table is Sheet1, filters(
ColXtr(1, RowCnd(CellValue, 1, StrCnd(shorter, 0)), 0)
));

which format it is:
(ooxml, no labels, table is Sheet1, filters(
ColXtr(1, RowCnd(CellValue, 1, StrCnd(shorter, 0)), 0)
));

I am trying to get it, but I am failing what is @1 like this