topic Re: Convert vertical to horizontal (like Generic load) in QlikView

Convert vertical to horizontal (like Generic load)

Nagaraju_KCS — Mon, 16 Aug 2021 17:17:48 GMT

Hi Experts,

User has done some manual work on excel, but facing issue with convert to qlik..

below screen Period to Revenue BPS is original data, remaining user were calculated (Period column has only 4 months) - but they were calculated JAN - DEC.

Period column highlighted March 2021, calculation will be start from March (March calculation should be Cashflow/12*0.5 - remaining greater months calculation should be Cashflow/12*1)

rest months also same behavior....

Any possibility UI or Backend ?

Thanks in Advance!

Re: Convert vertical to horizontal (like Generic load)

Nagaraju_KCS — Tue, 17 Aug 2021 02:57:31 GMT

Experts, anyone can give suggestions please ?

Re: Convert vertical to horizontal (like Generic load)

Norris6 — Wed, 18 Aug 2021 09:10:56 GMT

Click on the arrow below the "Paste" item and select "Transpose." Excel pastes in your copied rows as columns or your copied columns as rows.

Target Card Balance Now

Re: Convert vertical to horizontal (like Generic load)

Taoufiq_Zarra — Tue, 17 Aug 2021 12:24:19 GMT

@Nagaraju_KCS can you share the expected output also ?

Re: Convert vertical to horizontal (like Generic load)

Nagaraju_KCS — Tue, 17 Aug 2021 13:06:51 GMT

Thanks for reply!

Original Data: column A-E is my original data

OutPut: which I have highlighted in RED, I have to calculate in Qlik

Hope you understand!

Thanks in Advance.

Re: Convert vertical to horizontal (like Generic load)

Nagaraju_KCS — Tue, 17 Aug 2021 13:10:48 GMT

Hi Norries,

May be I didn't clear explanation.

Please note Period to Revenue BPS is original data, remaining columns have to calculate in Qlik.

Re: Convert vertical to horizontal (like Generic load)

Taoufiq_Zarra — Tue, 17 Aug 2021 14:00:02 GMT

do you have always one year ?

Re: Convert vertical to horizontal (like Generic load)

Nagaraju_KCS — Tue, 17 Aug 2021 16:21:19 GMT

Yes, application has only one year in future also

Re: Convert vertical to horizontal (like Generic load)

Taoufiq_Zarra — Tue, 17 Aug 2021 21:45:51 GMT

@Nagaraju_KCS it's a bit long, but you can optmize it

Input:

Script to use:

Input: LOAD * ,Num(Month(Date#(Period,'MMMM YYYY'))) as NumMonth,Num(Year(Date#(Period,'MMMM YYYY'))) as Year INLINE [ Period, Client Name, ISIN Number, Cashflow , Revenue BPS March 2021, ABC Cinema Ltd Pension Scheme & Optima 2 Pension Schem, IE00B1KQWK86, -166 053 , "0,65%" January 2021, ABC Cinema Ltd Pension Scheme & Optima 2 Pension Schem, IE00B1KQWK86, -157 240 , "0,65%" April 2021, ABC Cinema Ltd Pension Scheme & Optima 2 Pension Schem, IE00B1KQWK86, 22 , "0,67%" February 2021, ABC Cinema Ltd Pension Scheme & Optima 2 Pension Schem, IE00B1KQWK86, 52 , "0,65%" ]; join load Iterno() As Num AutoGenerate 1 While IterNo() <=12; Tmp: noconcatenate load * resident Input order by NumMonth, Num; drop table Input; Tmp2: noconcatenate load *,Num(if(NumMonth=Num,Cashflow/12*0.5,if(NumMonth<Num,Cashflow/12)),'###0,00') as ValueTmp resident Tmp; drop table Tmp; CombinedGenericTable: load distinct Period, [Client Name], [ISIN Number], Cashflow , [Revenue BPS] resident Tmp2; DATA: Generic LOAD Period, [Client Name], [ISIN Number], Cashflow , [Revenue BPS], Date(Monthend(Makedate(Year,Num,1))) as MonthTmppp,ValueTmp resident Tmp2; drop table Tmp2; FOR i = NoOfTables()-1 to 0 STEP -1 LET vTable=TableName($(i)); IF WildMatch('$(vTable)', 'DATA.*') THEN LEFT JOIN ([CombinedGenericTable]) LOAD distinct * RESIDENT [$(vTable)]; DROP TABLE [$(vTable)]; ENDIF NEXT i

output:

Re: Convert vertical to horizontal (like Generic load)

peppers59 — Wed, 18 Aug 2021 12:09:17 GMT

Hello,

Please follow this steps, I am sure to get result through this steps:

Select the vertical data.
Type Ctrl C to copy.
Click in the first cell of the horizontal range.
Type Alt E, then type S to open the Paste Special dialog.
Choose the Transpose checkbox as shown in Figure 1.
Click OK.