topic Re: Dynamic for in QlikView

Dynamic for

suzel404 — Thu, 28 Apr 2016 20:53:26 GMT

Hi all,

How can I calculate dynamically the number of documents by type and store ?

I have a field that contains : {1,2,3,4,101,102,104,108,109} and I want to calculate the number of DOC_ID by TYPE_ID and STORE.

I want to calculate the number of documents only for the existing value of TYPE_ID : {1,2,3,4,101,102,104,108,109} and dynamically.

How can I do that ?

Thanks for your help.

Temp:

LOAD

DOCS_ID,

DATE,

TYPE_ID,

STORE

FROM C:\QVD\DOC_FOR.qvd (qvd);

MIN_MAX_DOCS:

LOAD

MIN(TYPE_ID) as MIN_TYPE_ID,

MAX(TYPE_ID) as MAX_TYPE_ID

FROM C:\QVD\DOC_FOR.qvd (qvd);

LET v_MIN_TYPE_ID = peek('MIN_TYPE_ID');

LET v_MAX_TYPE_ID = peek('MAX_TYPE_ID');

FOR i = $(v_MIN_TYPE_ID) TO $(v_MAX_TYPE_ID)

CONCATENATE

LOAD

COUNT(DOCS_ID) AS NB_DOCS_$(i),

// DATE,

// TYPE_ID,

STORE,

TYPE_ID AS TYPE_ID_$(i)

RESIDENT Temp WHERE EXISTS(TYPE_DOCUMENT_ID, $(i))

GROUP BY TYPE_ID, STORE;

DROP TABLE Temp;

DROP TABLE MIN_MAX_DOCS;

EXIT SCRIPT;

Re: Dynamic for

Peter_Cammaert — Thu, 28 Apr 2016 21:23:42 GMT

How about:

BaseData::

LOAD DOCS_ID,

DATE,

TYPE_ID,

STORE

FROM C:\QVD\DOC_FOR.qvd (qvd);

Counters:

LOAD TYPE_ID,

STORE,

Count(DOC_ID) AS NumberOfDocuments

RESIDENT BaseData

GROUP BY TYPE_ID, STORE;

DROP Tzble BaseData;

Best,

Peter

Re: Dynamic for

suzel404 — Thu, 28 Apr 2016 22:48:02 GMT

Hi Peter,

Thanks you for your response but I would like this :

LOAD TYPE_ID,

STORE,

Count(DOC_ID) AS NumberOfDocuments$(i)

RESIDENT BaseData

GROUP BY TYPE_ID, STORE;

$(i) = value of field TYPE_ID

Where i = {1,2,3,4,101,102,104,108,109}

In final, my Aggr table is like this:

LOAD TYPE_ID,

STORE,

NumberOfDocuments1

NumberOfDocuments2

NumberOfDocuments3

NumberOfDocuments4

NumberOfDocuments101

NumberOfDocuments102

NumberOfDocuments103

NumberOfDocuments104

NumberOfDocuments108

umberOfDocuments109

RESIDENT BaseData;

Re: Dynamic for

suzel404 — Fri, 29 Apr 2016 19:43:00 GMT

Hi,

Someone has an idea ?

Thanks for your response.

Regards.