Dynamic for

[suzel404]

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;

NEXT

DROP TABLE Temp;

DROP TABLE MIN_MAX_DOCS;

EXIT SCRIPT;

[Peter_Cammaert]

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

[suzel404]

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;

[suzel404]

Hi,

Someone has an idea ?

Thanks for your response.

Regards.