topic Re: Excel formulas to Qlik in QlikView

Excel formulas to Qlik

srujanaponnuru — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Excel formulas to Qlik

Sergey_Shuklin — Tue, 04 Sep 2018 06:06:32 GMT

Hello!

Here you go.

Re: Excel formulas to Qlik

srujanaponnuru — Tue, 04 Sep 2018 13:16:35 GMT

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.

Re: Excel formulas to Qlik

vamsee — Tue, 04 Sep 2018 13:32:10 GMT

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);

Re: Excel formulas to Qlik

srujanaponnuru — Wed, 05 Sep 2018 02:37:31 GMT

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.

Re: Excel formulas to Qlik

srujanaponnuru — Wed, 05 Sep 2018 02:37:48 GMT

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.

Re: Excel formulas to Qlik

vamsee — Wed, 05 Sep 2018 13:39:00 GMT

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/MappingFunctions/ApplyMap.htm

Syntax for Lookup:

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

Re: Excel formulas to Qlik

srujanaponnuru — Sat, 08 Sep 2018 18:53:46 GMT

‌thank you vamsi

Re: Excel formulas to Qlik

vamsee — Mon, 10 Sep 2018 13:52:45 GMT

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

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