topic Re: CrossTable/Transpose/Rotate in QlikView

CrossTable/Transpose/Rotate

BradleyIsaacs — Sat, 16 Nov 2024 21:15:27 GMT

Hi there

I always struggle with crosstables etc. PLease help me with this tricky transpose of data.

I just need it to have the following columns :

Customer, Market Segment, Estimate Version, YEar, Month, Qty, TurnOver

(File is attached)

Re: CrossTable/Transpose/Rotate

Saravanan_Desingh — Sat, 25 Apr 2020 22:57:22 GMT

Are you looking something like this?

mapQty: Mapping LOAD Qty, Year&'@@'&Month INLINE [ Qty, Year, Month Qty, 2014, Aug Qty1, 2014, Dec Qty2, 2014, Jul Qty3, 2014, Jun Qty4, 2014, Nov Qty5, 2014, Oct Qty6, 2014, Sep ]; mapTO: Mapping LOAD TO, Year&'@@'&Month INLINE [ TO, Year, Month Turnover, 2014, Aug Turnover1, 2014, Dec Turnover2, 2014, Jul Turnover3, 2014, Jun Turnover4, 2014, Nov Turnover5, 2014, Oct Turnover6, 2014, Sep ]; tab1: CrossTable (Key, Value, 3) LOAD Customer, [Market Sub Segment], [Estimate Version], Qty, Turnover, Qty1, Turnover1, Qty2, Turnover2, Qty3, Turnover3, Qty4, Turnover4, Qty5, Turnover5, Qty6, Turnover6 FROM [C:\Users\sarav\Downloads\Estimates.xls] (biff, embedded labels, header is 2 lines, table is Sheet1$); tab2: LOAD *, SubField(If(Key Like 'Qty*', ApplyMap('mapQty',Key),ApplyMap('mapTO',Key)),'@@',1) As Year , SubField(If(Key Like 'Qty*', ApplyMap('mapQty',Key),ApplyMap('mapTO',Key)),'@@',2) As Month Resident tab1; Drop Table tab1; Drop Field Key, Value;

Re: CrossTable/Transpose/Rotate

Saravanan_Desingh — Sat, 25 Apr 2020 23:25:21 GMT

Please check this.

mapQty: Mapping LOAD Qty, Year&'@@'&Month INLINE [ Qty, Year, Month Qty, 2014, Aug Qty1, 2014, Dec Qty2, 2014, Jul Qty3, 2014, Jun Qty4, 2014, Nov Qty5, 2014, Oct Qty6, 2014, Sep ]; mapTO: Mapping LOAD TO, Year&'@@'&Month INLINE [ TO, Year, Month Turnover, 2014, Aug Turnover1, 2014, Dec Turnover2, 2014, Jul Turnover3, 2014, Jun Turnover4, 2014, Nov Turnover5, 2014, Oct Turnover6, 2014, Sep ]; tab1: CrossTable (Key, Value, 3) LOAD Customer, [Market Sub Segment], [Estimate Version], Qty, Turnover, Qty1, Turnover1, Qty2, Turnover2, Qty3, Turnover3, Qty4, Turnover4, Qty5, Turnover5, Qty6, Turnover6 FROM [C:\Users\sarav\Downloads\Estimates.xls] (biff, embedded labels, header is 2 lines, table is Sheet1$); Left Join (tab1) LOAD Customer ,[Market Sub Segment] ,[Estimate Version] ,SubField(If(Key Like 'Qty*', ApplyMap('mapQty',Key)),'@@',1) As Year ,SubField(If(Key Like 'Qty*', ApplyMap('mapQty',Key)),'@@',2) As Month ,If(Key Like 'Qty*', Value) As Qty Resident tab1; Left Join (tab1) LOAD Customer ,[Market Sub Segment] ,[Estimate Version] ,SubField(If(Key Like 'Turnover*', ApplyMap('mapTO',Key)),'@@',1) As Year ,SubField(If(Key Like 'Turnover*', ApplyMap('mapTO',Key)),'@@',2) As Month ,If(Key Like 'Turnover*', Value) As Turnover Resident tab1;