topic Re: How to count text within a field in QlikView

How to count text within a field

kfahri342 — Wed, 14 Mar 2018 10:55:25 GMT

Hi Qlikers,

I have the underlying data

Type	Date [2018]
Type 1	Jan
Type 1 \| Type 2 \| Type 4	Feb
Type 3 \| Type 4	Mar
Type 1 \| Type 3 \| Type 4	Jan
Type 1	Feb

and I need Qlikview to show this:

Type	Jan	Feb	Mar
Type 1	2	2
Type 2		1
Type 3	1		1
Type 4	1	1	1

I thought Mapping would work, but there seems to be a conflict when the same type rolls up into the same name.

Any Ideas?

Many Thanks...Again

Kozan

Re: How to count text within a field

PrashantSangle — Wed, 14 Mar 2018 10:57:52 GMT

Hi,

In backend use trim(subfield(Type, '|')) as Type

Regards

Re: How to count text within a field

Nagaraju_KCS — Wed, 14 Mar 2018 11:29:17 GMT

Try this,

CountText:

Load *, Trim(subfield(Type, '|')) as NewType;

LOAD * INLINE [

Type, Month

Type 1, Jan

Type 1 | Type 2 | Type 4, Feb

Type 3 | Type 4, Mar

Type 1 | Type 3 | Type 4, Jan

Type 1, Feb

];

Load NewType,

Month

Resident CountText;

DROP Table CountText;

Take Pivot table Dimensions = NewType and Month

Expression = count(NewType)

Re: How to count text within a field

kfahri342 — Wed, 14 Mar 2018 12:53:41 GMT

Thanks Raju,

However this seems restricted - the document I am working on have over 3000 entries and that can change over time. Is there a way to create the above that can take this into account?

Kozan

Re: How to count text within a field

kfahri342 — Wed, 14 Mar 2018 12:54:44 GMT

Hi Prashant,

Apologies, my knowledge on Qlikview isn't the best. Are you able to elaborate on this.

Also, the data above is only a sample - the document I am actually working on has far more entries that can change over time.

Re: How to count text within a field

PrashantSangle — Wed, 14 Mar 2018 12:59:40 GMT

Hi,

You have to implement script as qv_testing‌ suggested. Just try what he suggested if it is not working then let us know.

Regards,

Prashant

Re: How to count text within a field

Peter_Cammaert — Wed, 14 Mar 2018 13:04:57 GMT

That was just an example using the data you provided in your OP embedded in an INLINE LOAD.

Replace the INLINE [...] part with a FROM YourSource clause and the data processing will follow whatever is present in your source data at a particular point in time.

Re: How to count text within a field

kfahri342 — Wed, 14 Mar 2018 13:25:00 GMT

Thanks, I have tried the above - but I don't seem to get the NewType in my dimensions.

Re: How to count text within a field

Peter_Cammaert — Wed, 14 Mar 2018 13:36:55 GMT

If you want us to help with the integration of this solution into your script, post your script or an example document in this thread. Thanks

Re: How to count text within a field

kfahri342 — Wed, 14 Mar 2018 15:21:05 GMT

I wish I could. Unfortunately it is work related and therefore strictly prohibited.

Is it possible to have the formula recreated without the Inline piece? Lets say my underlying data is called Database.xls for example.

Perhaps that is where I am going wrong

Re: How to count text within a field

PrashantSangle — Wed, 14 Mar 2018 15:39:18 GMT

Post your script..

Regards,