topic Re: New Column Creation in QlikView

New Column Creation

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

Hi Everyone,

I have a requirement that i want to create a new output column using existing Equation column .

The output column is replaced with id names excluding _YY. Please find the attached sample data.

ID	ID name	Equation	New Output Column
124	Days Sales	([91_YY])/([225_YY]/365)	(Annual/ Annual days/365)
91	Annual	Null	Null
225	Annual days	Null	Null
4826	Sales	(([225_YY]-[225_YY-1])/[225_YY-1])*100	(([Annual days]-Annual days-1])/[Annual days-1])*100
729	Inventory	([147_YY]/[236_YY])*100	([Inventory Carrying]/Total Product Revenue ])*100
147	Inventory Carrying	Null	Null
236	Total Product Revenue	Null	Null

For example :

([147_YY]/[236_YY])*100

ID 147= Inventory Carrying

ID 236= Total Product Revenue

New output column should be:(Inventory Carrying/Total Product Revenue)*100

Thanks in advance

Re: New Column Creation

padmanabhan_ram — Thu, 12 Oct 2017 08:45:03 GMT

Hi Kavya,

can you please share the expected table in an excel with sample data and output including calculations.

Br,

Padmanabhan

Re: New Column Creation

Anonymous — Thu, 12 Oct 2017 12:15:07 GMT

Hi,

Please find the attached sample data and output column.

New Output Column

(Annual/ Annual days/365)

Null

(([Annual days]-Annual days-1])/[Annual days-1])*100

([Inventory Carrying]/Total Product Revenue
])*100

Null

Calculation:

([147_YY]/[236_YY])*100

ID 147= Inventory Carrying

ID 236= Total Product Revenue

New output column should be:(Inventory Carrying/Total Product Revenue)*100

Thanks,

Kavya

Re: New Column Creation

padmanabhan_ram — Thu, 12 Oct 2017 13:01:47 GMT

hi kavya,

I can understand that 147, 236 are ID's but what is _YY?

Br,

Padmanabhan

Re: New Column Creation

Anonymous — Fri, 13 Oct 2017 03:30:14 GMT

HI Padmanaban,

_YY is in Equation column .The output column should be replaced with id names (Annual/ Annual days/365) and _YY should be removed from Equation column..

For example :

([147_YY]/[236_YY])*100

(Inventory Carrying/Total Product Revenue)*100

Re: New Column Creation

padmanabhan_ram — Fri, 13 Oct 2017 09:51:26 GMT

Hi Kavya,

please find the attached file and let me if this is what is expected.

Br,

Padmanabhan

Re: New Column Creation

Anonymous — Fri, 13 Oct 2017 10:00:24 GMT

Thanks for your reply.

I am expecting new column to be like this (Inventory Carrying/Total Product Revenue)*100.

Re: New Column Creation

padmanabhan_ram — Fri, 13 Oct 2017 10:40:33 GMT

Please find the attached, let me know if it matches your expectation.

Br,

Padmanabhan

Re: New Column Creation

Anonymous — Mon, 16 Oct 2017 06:35:47 GMT

Hi Padmanabhan,

Thanks for the answer.

I am having more than 1000 Id's .Each time i cannot hard code the ID.Please let me know is there any way to replace ID with ID names,

Thanks

Re: New Column Creation

padmanabhan_ram — Mon, 16 Oct 2017 10:06:19 GMT

Hi Kavya,

You ca replace the ID's with ID Name in the IF statements. Maintain the ID Names in the excel with variables and replace the names by variables just for better performance.

I hope you do not have 1000 ID names, if so then you have to find some other way to do it.

Br,

Padmanabhan

Re: New Column Creation

Anonymous — Mon, 16 Oct 2017 11:58:16 GMT

Thank you so much padmanabhan