topic Re: For each loop value in field in QlikView

For each loop value in field

suzel404 — Mon, 02 May 2016 09:04:16 GMT

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.

Re: For each loop value in field

sunny_talwar — Mon, 02 May 2016 09:08:49 GMT

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

Re: For each loop value in field

swuehl — Mon, 02 May 2016 09:20:09 GMT

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;

Re: For each loop value in field

Kushal_Chawda — Mon, 02 May 2016 09:43:22 GMT

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;

Re: For each loop value in field

suzel404 — Mon, 02 May 2016 10:18:29 GMT

Thank you guys.