Excel formulas to Qlik

[srujanaponnuru]

Hi All,

I have requirement. The logic is in excel and we need to write the logic in qlik.

Sample attached is the excel file.

I have to write the logic for Coulumn C and D. Please help me how to write in qlik,

thanks in advance

[Sergey_Shuklin]

Hello!

Here you go.

[srujanaponnuru]

Thank you for replying but the data will change every month for us. It is not static , so cant use inline tables here.

Could you please help me to do it dynamically.

[vamsee]

May be Try:

Source:
LOAD
[Row Labels],
Fabs([Sum of Value]) as Value
FROM

(ooxml, embedded labels, table is Sheet1)
Where
Not Match( [Row Labels], 'Grand Total');

Concatenate

Temp:
NoConcatenate
LOAD

SUM([Sum of Value]) as Value
FROM

(ooxml, embedded labels, table is Sheet1)
Where
Not Match( [Row Labels], 'Grand Total')
;


Let vValue= Peek('Value', 0, 'Temp');

Drop Table Temp;

Totals:
Concatenate(Source)
LOAD
'Grand Total' as [Row Labels],
$(vValue)  as  Value
AutoGenerate(1);

[srujanaponnuru]

Hey Thank you so much. Its working but there is a small doubt again.

In my excel file, columns H, I , J is  a master table (i.e history data), so i need to load this as well and map or vlookup with D Column.

Could u please help me out how to map in qlikview. As i can do it in excel , it is little difficult writing in qlik as i am very new to qlik. So facing some problem with this.

Thanks in advance.

[srujanaponnuru]

Hey Thank you so much. Its working but there is a small doubt again.

In my excel file, columns H, I , J is  a master table (i.e history data), so i need to load this as well and map or vlookup with D Column.

Could u please help me out how to map in qlikview.

[vamsee]

Paste the below code post the code mentioned earlier.

Historical_Value:
Mapping LOAD
[Row Labels1],
[Sum of value]
FROM
[SAMPLE DATA.xlsx]
(ooxml, embedded labels, table is Sheet1);

Historical_ABS_Value:
Mapping LOAD
[Row Labels1],

[ABS Sum of Value]
FROM
[SAMPLE DATA.xlsx]
(ooxml, embedded labels, table is Sheet1);
Final:
NoConcatenate
LOAD
[Row Labels],
Value,
ApplyMap('Historical_Value',[Row Labels] ,0) as Historical_Value,
ApplyMap('Historical_ABS_Value',[Row Labels] ,0) as Historical_ABS_Value

Resident Source;
DROP Table Source;

Syntax for Applymaps:

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/MappingFunctio...

Syntax for Lookup:

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/InterRecordFun...

[srujanaponnuru]

‌thank you vamsi

[vamsee]

Great, please close the thread by marking the correct response also.

https://community.qlik.com/docs/DOC-14806