topic Re: Report building in QlikView

Report building

priyarane — Tue, 21 Apr 2015 19:32:17 GMT

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.

Re: Report building

petter — Tue, 21 Apr 2015 22:04:41 GMT

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.

Re: Report building

priyarane — Wed, 22 Apr 2015 05:11:55 GMT

This is really helpful, Petter. Thank you

Re: Report building

priyarane — Wed, 22 Apr 2015 09:33:02 GMT

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.

Re: Report building

petter — Wed, 22 Apr 2015 10:03:56 GMT

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

Re: Report building

priyarane — Wed, 22 Apr 2015 10:18:42 GMT

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

Re: Report building

priyarane — Wed, 22 Apr 2015 10:37:07 GMT

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

Re: Report building

petter — Wed, 22 Apr 2015 11:17:24 GMT

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.

Re: Report building

petter — Wed, 22 Apr 2015 11:19:38 GMT

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.

Re: Report building

priyarane — Wed, 22 Apr 2015 11:40:54 GMT

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

Re: Report building

petter — Wed, 22 Apr 2015 12:02:31 GMT

ooxml means simply Office Open XML - which is XLSX-format ....

The @1 and @2 simply means column 1 and column 2 from the source since I was lazy and didn't rename them in the wizard... So it is just to get a simple table with just the RowHeaders ...

Re: Report building

priyarane — Wed, 22 Apr 2015 12:13:04 GMT

Hi Petter,

But I am failing as attached.

Re: Report building

petter — Wed, 22 Apr 2015 13:21:45 GMT

You should not specify XML as filetype in the wizard - just select the Excel (XLSX) ... which is indeed an application of XML-standard but for the specific need of Excel ... being zipped and having its specific subset of tags as valid...

Re: Report building

petter — Wed, 22 Apr 2015 22:40:54 GMT

Actually there are some inconsistencies in the first Source.xlsx and this Test (2).xlsx ....

There is a blank column insert at the column C in the second file. There is also another header text in the

second file for As of Date which is not consitent with the first one. And lastly the Sheet name is different between

the two files. In the first it is Sheet1 and in the last file it is Sheet3.

These are all inconsitencies that will make it much harder to develop a robust load script. Is this inconsitencies that

are only in your test and sample data or will it be with your real-world spreadsheets that you will prosess?