For each loop value in field

[suzel404]

Hi all,

I have different value in field TYPE : {1,2,3,4,10,11,13,16,19}

I want to calculate the number of docs by date, store and type (for each type)

FOR EACH i=1 TO MY LIST VALUE IN FIELD TYPE

LOAD

     DATE,

     STORE,

     TYPE,

     Count(DOCS) AS NB_$(i)

FROM QVD GROUP BY DATE, TYPE, STORE;

I would like a qvd with :

LOAD

     DATE,

     STORE,

     TYPE,

     NB_1,

     NB_2,

     NB3,

     NB_4,

     NB_10,

     NB_11,

     NB_13,

     NB_16,

     NB_19

FROM QVD;

How can I do that?

Thanks for your help.

Regards.

[sunny_talwar]

May be like this:

Table:

LOAD 0 as Dummy

AutoGenerate 0;

FOR each i in 1,2,3,4,10,11,13,16,19

    Join (Table)

    LOAD DATE,

                STORE,

                TYPE,

                Count(DOCS) as NB_$(i)

    FROM QVD

    Where TYPE = $(i)

    GROUP BY DATE, TYPE, STORE;

NEXT

STORE Table into FinalTable.qvd (qvd);

UPDATE: Added a Where Statement

[swuehl]

Try to avoid a FOR LOOP in your script. I also would try to avoid the crosstable like data structure, instead, create two fields for  SUBTYPE and COUNT.

I haven't really understood how your input records look like and what you want to do with them, but maybe something along these lines:

INPUT:

LOAD

     DATE,

     STORE,

     TYPE,

     DOCS,

     SubField( Purgechar(TYPE,'{}'), ',') as SUBTYPE

FROM QVD.qvd (qvd);

RESULT:

LOAD

     DATE,

     STORE,

     SUBTYPE,

     COUNT(DOCS) as COUNT

RESIDENT INPUT

GROUP BY DATE, STORE, SUBTYPE;

DROP TABLE INPUT;

[Kushal_Chawda · Accepted solution]

try this

Data:

LOAD TYPE

FROM QVD ;

Final:

LOAD 1 as Junk

autogenerate 1;

FOR Each i in FieldValueList('TYPE')

Join(Final)

LOAD

    DATE,

    STORE,

    TYPE,

    Count(DOCS) AS NB_$(i)

FROM QVD

WHERE TYPE ='$(i)'

GROUP BY DATE, TYPE, STORE;

NEXT i

DROP Table Data;

DROP Field junk;

STORE Final into Path\FinalQVD.QVD;

[suzel404]

Thank you guys.