Linking/Mapping two tables

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

Dear Felip, Wishvanath, & all dear experts,

I seek your expert knowledge to overcome my issue!. In fact Mr Vishwanath, Ranathunga helped me towards this to some way. I may again present you the case.

I have a requirement to get the higher approval for expenses which have been exceeded the budgetary allowances! This I have to do quarterly. Easily I may arrange the 1st Table with lots of expressions basically actual & the budget.(Need to consider the cumulative). For the reasons for each exceeded expenses I have to go through the list & find out. Once I arranged that I may arrange them in a table (2nd table). Here as you can see some reasons are there for all the Branch codes & for other may be 1st or 2nd . My requirement is as follows!.

Pick those reasons to be appeared before appear each expenses items which are having negative cumulative variance!

If I use the Concat, it seems all the reasons are linked to all Branches.

Since many Branches are involved, I cannot do this for individual Branches if so actually no issue because I can pick reasons from separate table arranged for that particular Branch & so on. But this is very long process. Hence please advice, help me to achieve this.

Each Branch should pick the relevant reasons highlighted in the reason table

There are two common field such as BR_CODE & TYPE,

Variance table

BR_CODE	CATEGORY	NOTE_CODE	TYPE	ACT_MONTH	BUD_MONTH	VARIANCE_MONTH	ACT_CUMULATIVE	BUD_CUMULATIVE	VARIANCE
RA	STAFF EXPENSES	500	STAFF SALARIES	988,677.41	973,840.00	-14,837.41	7,507,077.41	7,480,400.00	-26,677.41
RA	STAFF EXPENSES	513	STAFF WELFARE	45,159.06	28,000.00	-17,159.06	242,390.18	224,000.00	-18,390.18
RA	SELLING EXPENSE	702	BUSINESS PROMOTION	89,324.79	28,725.00	-60,599.79	259,798.33	265,000.00	-29,998.33
EM	STAFF EXPENSES	500	STAFF SALARIES	1,000,000.00	973,840.00	-14,837.41	8,500,000.00	7,480,400.00	-26,677.41
EM	STAFF EXPENSES	513	STAFF WELFARE	45,159.06	28,000.00	-17,159.06	350,000.00	500,000.00	-18,390.18
EM	SELLING EXPENSE	702	BUSINESS PROMOTION	89,324.79	28,725.00	-60,599.79	259,798.33	229,800.00	-29,998.33

Table(with reasons)

BR_CODE	TYPE	REASON_1	REASON_2	REASON_3	REASON_4	REASON_5
RA	STAFF SALARIES	Unbudgeted_Expense	New_Recruits_MES	New_Recruits_Underwriters	New_Recruits_ASM	New_Recruits_accounts
EM	STAFF SALARIES	Unbudgeted_Expense	New_Recruits_MES
RA	STAFF WELFARE	Wedding Gift_UW	Wedding Gift_ME	Wedding Gift_Manager	Tea related Expenses	Staff_Uniform_Peon
EM	STAFF WELFARE				Tea related Expenses
RA	BUSINESS PROMOTION	Town_Storming	Customer Get together	Finance company cocktal	Donation
EM	BUSINESS PROMOTION	Town_Storming

Regards

Neville

Re: Linking/Mapping two tables

nevilledhamsiri — Sun, 01 Oct 2017 17:46:01 GMT

Dear Wishwarath,

Any idea towards this!. If we do the join can it be made possible. Since two common fields are there, can it be dane

Neville

Re: Linking/Mapping two tables

qliksus — Mon, 02 Oct 2017 05:27:25 GMT

Since both the tables are having a common field BR_CODE just connect the two tables using that and use the below expression

If ( Variance<0 , Maxstring(REASON_1) )

Re: Linking/Mapping two tables

dinuwanbr — Mon, 02 Oct 2017 05:49:03 GMT

hi,

Still m not so clear about what u want.

Is this fit to ur requirement?

Rgds,

Tharindu

topic Re: Linking/Mapping two tables in QlikView

Linking/Mapping two tables

Re: Linking/Mapping two tables

Re: Linking/Mapping two tables

Re: Linking/Mapping two tables