Convert vertical to horizontal (like Generic load)

[qv_testing]

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!

[qv_testing]

Experts, anyone can give suggestions please ?

[Norris6]

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

[Taoufiq_Zarra]

@qv_testing  can you share the expected output also ?

[qv_testing]

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.

[qv_testing]

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.

[Taoufiq_Zarra]

do you have always one year ?

[qv_testing]

Yes, application has only one year in future also 

[Taoufiq_Zarra]

@qv_testing  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:

[peppers59]

Hello,

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

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