Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
Experts, anyone can give suggestions please ?
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.
@qv_testing can you share the expected output also ?
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.
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.
do you have always one year ?
Yes, application has only one year in future also
@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:
Hello,
Please follow this steps, I am sure to get result through this steps: