topic Re: Transpose data in model? in QlikView

Transpose data in model?

Wed, 25 Nov 2020 16:16:04 GMT

My data is such that sum( ACH_TXN)+sum(BAL_TXN)+sum(GE_TXN)+sum(THIRD_CC_TXN)+sum(THIRD_DC_TXN)+sum(PP_CR_TXN)

=net_txn_cnt.

I want to transpose all my transactions such that I can put them into one field say 'MIX', so that I could use that field in a stacked bar chart.

But I'm not sure how to transpose the field in my model. Can someone help me with this.

Thanks for the help.

Regards,

Sachin

Re: Transpose data in model?

Anonymous — Wed, 23 Mar 2016 16:58:15 GMT

Is the attached what you are after ?

p.s. I have reduced and scrambled your data: Preparing examples for Upload - Reduction and Data Scrambling

It could be prudent for you to remove and replace your original qvw with one similarly reduced and scrambled.

Re: Transpose data in model?

Wed, 23 Mar 2016 17:04:35 GMT

Hi Bill, that's the stacked chart I want but I don't want to have so many expressions, just want to consolidate my code in a such a way that I have only one expression for all the transactions. Because this solution is part of a larger combo chart and its already very complicated.

Hope that makes sense.

Re: Transpose data in model?

Anonymous — Wed, 23 Mar 2016 17:11:41 GMT

In that case you may well need to do aThe Crosstable Load

Have a look at this blog post by HIC - do you reckon it is what you need ?

Re: Transpose data in model?

Wed, 23 Mar 2016 17:13:35 GMT

Yes, this is how I want it. Would it be possible to send a sample code.

Re: Transpose data in model?

Anonymous — Wed, 23 Mar 2016 17:21:23 GMT

Pasted below is the sample code from the blog post :

tmpData:

Crosstable (MonthText, Sales)

Load Product, [Jan 2014], [Feb 2014], … From Data;

Final:

Load Product,

Date(Date#(MonthText,'MMM YYYY'),'MMM YYYY') as Month,

Sales

Resident tmpData;

Drop Table tmpData;