topic Re: Automate excel Data in QlikView in QlikView

Automate excel Data in QlikView

abhaysingh — Wed, 02 Sep 2015 05:24:09 GMT

Hi all,

I have reports in attached Sample Format. Can I automate this report??

Do not Require Total at the end of the report..
also do not want 1st Raw of Data.

Is it possible kindly suggest..

Regards

Abhay

Re: Automate excel Data in QlikView

petter — Wed, 02 Sep 2015 05:38:28 GMT

Yes there is several ways of automating something like you have there. But you should state more details about your requirements so people give you the right path to follow...

1) Will you run in on a server?

2) Will your user run the Ajax client / full browser?

3) Should the final report also be an XLSX-file? or is it just your way of illustrating the looks of the result?

4) Should the report pop-up on the screen for the user?

5) Should it be many of these reports produced simultanuously for all companies on a schedule or on demand by the user?

6) Do you need to achieve this without purchasing things like NPrinting licenses?

Re: Automate excel Data in QlikView

abhaysingh — Wed, 02 Sep 2015 06:13:52 GMT

Hi Petter,

The sample report I have attached is Raw File Format in which I am getting data.

1- till now working on local but yes once application goes live the same format data i'll got on server.

2- Preferred Ajax Client

3- No Final report not in this format.

4-No

5- No Only I need to fetch data from these reports and produce my own reports.

6- I don't have NPrinting yet

Regards

Abhay

Re: Automate excel Data in QlikView

settu_periasamy — Wed, 02 Sep 2015 06:17:04 GMT

Hi,

if you want to fetch the table only from that source, may be try this

LOAD Num(SNo) as SNo,

Date(Date) as Date,

[Debit In Company Currency],

[Credit In Company Currency],

Division,

[Cost Centre]

FROM

QVcomm\TestFormatCommunity.xlsx

(ooxml, embedded labels, table is Sheet1, filters(

Remove(Row, Pos(Top, 4)),

Remove(Row, Pos(Top, 3)),

Remove(Row, Pos(Top, 2)),

Remove(Row, Pos(Top, 1))

))

WHERE(IsNum(SNo));

Re: Automate excel Data in QlikView

petter — Wed, 02 Sep 2015 06:45:13 GMT

Here is a way of reading the complete information into a table - including the company name, account group and reporting period from and to:

RawTable:
LOAD A,
    B,
    C,
    D,
    E,
    F
FROM

(ooxml, no labels, table is Sheet1);


vCompany = Peek('A',0);


vFrom = Trim( TextBetween( Peek('A',3), 'From:' , 'To:') );
vTo = Trim( TextBetween( Peek('A',3), 'To:' , Chr(10) ) ) ;
vAccountGroup = Trim( SubField( Peek('A' , 3 ) , 'AccountGroup:' , 2 ) ) ;


Numbers:
LOAD
  '$(vCompany)' AS Company,
  '$(vFrom)' AS FromDate,
  '$(vTo)' AS ToDate,
  '$(vAccountGroup)' AS AccountGroup,
  Num(A) as SNo,
    Date(B) as Date,
    C AS [Debit In Company Currency],
    D AS [Credit In Company Currency],
    E AS Division,
    F AS [Cost Centre]
RESIDENT RawTable
WHERE
IsNum(A)
AND
RecNo() > 4;

DROP TABLE RawTable;

You can in this way extend this reading so you read many Excel reports into the same table since each row is qualified by which company, reporting period and account group they refer to. Then you can make many visualizations and reports across companies and other dimensions.

Re: Automate excel Data in QlikView

petter — Wed, 02 Sep 2015 06:55:31 GMT

BTW here is the sample app illustrating what I mentioned above