Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customers, Partners & Luminaries only: You're invited to a Data Analytics Roadmap session. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

How to count text within a field

Hi Qlikers,

I have the underlying data

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

and I need Qlikview to show this:

TypeJanFebMar
Type 122
Type 21
Type 311
Type 4111

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

10 Replies
Highlighted

Hi,

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

Regards

Great dreamer's dreams never fulfilled, they are always transcended.
Highlighted
Specialist II
Specialist II

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)

Highlighted
Contributor III
Contributor III

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

Highlighted
Contributor III
Contributor III

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.

Highlighted

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

Great dreamer's dreams never fulfilled, they are always transcended.
Highlighted

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.

Highlighted
Contributor III
Contributor III

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

Highlighted

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

Highlighted
Contributor III
Contributor III

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