Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi,
In backend use trim(subfield(Type, '|')) as Type
Regards
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)
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
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.
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
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.
Thanks, I have tried the above - but I don't seem to get the NewType in my dimensions.
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
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