topic Re: RE:after-before in QlikView

RE:after-before

Chanty4u — Fri, 18 Mar 2016 06:18:00 GMT

Hi all,

I want to know the status of the document?

it means

how to know?

1)the data after_reload ?

2)the data before_reload?

3)no. of rows?

4)no.of columns?

thanks in advance:

Re: RE:after-before

Kushal_Chawda — Fri, 18 Mar 2016 06:23:01 GMT

data_after_reload and data_before_reload ? Do you mean to know number of rows before and after reload?

Re: RE:after-before

Chanty4u — Fri, 18 Mar 2016 06:26:37 GMT

for example

if i load excel data into qlikview.

i have some amount of data.

so how shud i knw?

Re: RE:after-before

avinashelite — Fri, 18 Mar 2016 06:29:45 GMT

you could use the document analyzer rite ??

or if you want to have in the script level than for every reload you need calculate the number of rows and columns and store it in the QVD so that you can make a comparison of the same

Re: RE:after-before

jagan — Fri, 18 Mar 2016 06:34:38 GMT

Hi,

Try NoOfRows() to get the count of rows in the table.

Example:

Data:

LOAD

FROM DAtaSource;

LET vRowsCount = NoOfRows('Data');

Regards,

jagan.

Re: RE:after-before

Chanty4u — Fri, 18 Mar 2016 06:35:01 GMT

hi avi,

i hve used below code

let before_reload=now();

//load from QVD's

Employees:

LOAD Series,

EmpID,

Identifier,

[Last Name],

[First Name],

Title,

[Hire Date],

Office,

Extension,

[Reports To],

[Year Salary]

FROM

..\Datasources\Employees_large.qvd

(qvd);

let after_reload = now();

but i dont know how can i see that defined variable values in front end?

Re: RE:after-before

jagan — Fri, 18 Mar 2016 06:35:17 GMT

Hi,

Try NoOfFields() to get the count of fields in the table.

Example:

Data:

LOAD

FROM DAtaSource;

LET vFieldsCount = NoOfFields('Data');

Regards,

jagan.

Re: RE:after-before

Chanty4u — Fri, 18 Mar 2016 06:39:53 GMT

did u chk the code which i posted above?

i can i see in the front end mentioned variables?

Re: RE:after-before

Kushal_Chawda — Fri, 18 Mar 2016 06:41:39 GMT

Employee:

Load *

From Table;

let vNoOfRows = NoOfRows('Data');

Details:

Load now() as Time,

$(vNoOfRows ) as Rows,

'Employee' as TableName

autogenerate 1;

Store Details into Path\DataLoadDetails.qvd;

drop table Details;

// Comment the Details Table code once you have created the QVD first time and run now the below code

Details:

Load now() as Time,

$(vNoOfRows ) as Rows,

'Employee' as TableName

autogenerate 1;

concatenate(Details)

Load Time,

Rows,

TableName

From Path\DataLoadDetails.qvd(qvd);

Store Details into Path\DataLoadDetails.qvd;

Create the Table box in front end and add the columns from Details table. You will be able to see the number of rows for that table during each reload.

If you want to do it for multiple table , use for loop.

Re: RE:after-before

jagan — Fri, 18 Mar 2016 06:44:51 GMT

Hi,

Create Text objects and put those variables or Settings -> variable Overview and check the value.

Regards,

jagan.

Re: RE:after-before

tamilarasu — Fri, 18 Mar 2016 06:49:54 GMT

Hi Chanty,

You can use below formulas in text box (Front end).

='No of Rows ' & sum($Rows)

='No of Fields ' & sum($Fields)

Re: RE:after-before

Chanty4u — Fri, 18 Mar 2016 07:05:59 GMT

hi jagan,

i hve used this in text object

Is this correct syntax?

='Time needed for reload: ' & date([after_reload]-[before_reload],'mm:ss') & chr(10)&chr(10)&

'Rows:' & count(DISTINCT Identifier) & chr(10) & 'Columns: 11'

Re: RE:after-before

Chanty4u — Fri, 18 Mar 2016 07:07:53 GMT

it is only for rows nd fileds?

for table i need to see no.of rows nd columns?

Re: RE:after-before

Gysbert_Wassenaar — Fri, 18 Mar 2016 07:07:56 GMT

Everytime you load data you will have to create a file (at the end of the script so all your real data is loaded) in which you store how many records your tables contain at the end of that run. During the next reload you can load this data again and at the end add the new record counts again to that file. Using the data from this file you can compare between one reload and any previous reload.

Something like this should get you started:

For i = 0 to NoOfTables()-1

  LET vTableName = TableName($(i));

  For j = 1 To NoOfFields('$(vTableName)')

  LET vFieldName = FieldName($(j),'$(vTableName)');

  TableStats:
  LOAD
     '$(vTableName)' as TableName,
     NoOfRows('$(vTableName)') as RowCount,
     NoOfFields('$(vTableName)') as FieldCount,
     '$(vFieldName)' as FieldName,
     FieldValueCount('$(vFieldName)') as FieldValueCount
  AUTOGENERATE 1
  ;

  Next

Next

STORE TableStats INTO TableStats.qvd (qvd);

Re: RE:after-before

Chanty4u — Fri, 18 Mar 2016 07:10:12 GMT

tq gysbert.

but evry time i need to do this?

Re: RE:after-before

jagan — Fri, 18 Mar 2016 07:11:53 GMT

Yes Correct. You can also use like this. If you have multiple tables then you can create a table using autogenerate and insert the records with this variables.

Regards,

Jagan.

Re: RE:after-before

Chanty4u — Fri, 18 Mar 2016 07:14:12 GMT

wen i use the abve code in text object shwng below

Re: RE:after-before

Gysbert_Wassenaar — Fri, 18 Mar 2016 07:15:16 GMT

Yes, everytime you reload. Because during a reload all data is discarded first and then new data is loaded. Qlikview does not keep track itself of (meta)data of previous loads.

Re: RE:after-before

Chanty4u — Fri, 18 Mar 2016 07:17:14 GMT

thanks for great explanation.

Re: RE:after-before

tamilarasu — Fri, 18 Mar 2016 07:20:14 GMT

You can create a chart like below.

Dimension:

$Table

Expressions:

=Sum($Rows)

=Sum($Fields)

If you want the data count "before reload", you need to store the data count somewhere (in a variable or text file). While reload, you can retrieve and store in a new variable again.