Automate excel Data in QlikView

[abhaysingh]

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

[petter]

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?

[abhaysingh]

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

[settu_periasamy]

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));

[petter]

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.

[petter]

BTW here is the sample app illustrating what I mentioned above