topic Re: Creating a new field from multiple tables in QlikView

Creating a new field from multiple tables

momofiore — Mon, 16 Jul 2018 15:24:48 GMT

Hey guys

is there any problem with this script? im trying to create a new field cold Is_AutoCap_NewIt2_3 with the help of 3 field from 3 tables:

-IsAutoCap from FactTicket

-ActivitySDTier from FactTicketActivity

-[Catégorie H2] from Applications

after the script done i just get 'NO' for Is_AutoCap_NewIt2_3

this script works perfectly when i try it with with 3 field from the same tables. but not defferent tables

Temporaire:

LOAD %TicketKey,%CIApplicationName,Ticket,IsAutoCap

Resident FactTicket;

Left Join(Temporaire)

LOAD %TicketKey,ActivitySDTier

Resident FactTicketActivity;

Left Join(Temporaire)

LOAD %CIApplicationName,[Catégorie H2]

Resident Applications;

Left Join(Temporaire)

LOAD %TicketKey,%CIApplicationName,

if(IsAutoCap =1 AND ActivitySDTier =3 AND [Catégorie H2] ='NEW IT T2','YES','NO') as Is_AutoCap_NewIt2_3

Resident Temporaire;

Left Join (FactTicket)

LOAD %TicketKey,%CIApplicationName,Is_AutoCap_NewIt2_3

Resident Temporaire;

Drop table Temporaire;

here you can see the result

stevedark‌

Re: Creating a new field from multiple tables

YoussefBelloum — Mon, 16 Jul 2018 15:28:39 GMT

Hi,

on the image attached above, your IsAutoCap field is null, so it is normal that it gives you NO

Re: Creating a new field from multiple tables

momofiore — Mon, 16 Jul 2018 16:15:00 GMT

there is data yet in my table

Re: Creating a new field from multiple tables

YoussefBelloum — Mon, 16 Jul 2018 20:52:52 GMT

‌ok, would you be able to share a portion of your data to make some tests with it ?

Re: Creating a new field from multiple tables

momofiore — Tue, 17 Jul 2018 08:41:12 GMT

Hey youssefbelloum‌

yes sure. table FactTicket:

%TicketKey	IsAutoCap	CloseDate	%CIApplicationName
876552	0	42096,3691	ONROUTE
876562		41732,6601	ORDERCARE
876568	1	41732,6601	ORDERCARE
877042		41655,5573	OPSYSWEB
877095	0	41655,5573	CFM
877095		42124,395	CFM
877165		41726,6452	ORDERCARE
877974	1	41794,618
877974		41660,6293
879242	0	41772,7227	ORDERCARE
880533			ORDERCARE
882369	1	42285,6157	ORDERCARE
883010		42285,6157	ORDERCARE
883319	1	41772,7198	ORDERCARE
883893		41772,7187	ORDERCARE
883919	1	41892,3912	ORDERCARE
885891	0	41956,7346	ORDERCARE
889089	0	42285,6305	ORDERCARE
889387		42144,3816	INLAB
889415	1	42144,3349	ORDERCARE

table FactTicketActivity:

%TicketKey	ActivitySDTier	Group
0		FR_EUR_BUILD/DEV_OM
876562	3	FR_EUR_BUILD/CONVERGENCE
876568	3	FR_EUR_BUILD/OM
877042	3	FR_EUR_BUILD/OPW_OPSYSWEB
877095	3	FR_EUR_BUILD/CONVERGENCE
877095		FR_EUR_BUILD/CONVERGENCE
877165	3	FR_EUR_BUILD/CONVERGENCE_BENO
877974	2	FR_EUR_BUILD/FIN_CFM
877974		FR_EUR_BUILD/FIN_CFM
879242	3	FR_EUR_BUILD/CONVERGENCE
880533	1	FR_EUR_BUILD/CONVERGENCE
882369	3	FR_EUR_BUILD/CONVERGENCE
883010	3	FR_EUR_BUILD/CONVERGENCE
883319	2	FR_EUR_BUILD/OM
883893	3	FR_EUR_BUILD/CONVERGENCE
883919	0	FR_EUR_BUILD/CONVERGENCE
885891	3	FR_EUR_BUILD/OM
889089	1	FR_EUR_BUILD/OM
889387	3	FR_EUR_BUILD/LAB.MGT
889415	2	FR_EUR_BUILD/EDI

table Application:

%CIApplicationName	Catégorie H2
ONROUTE	New IT T2
ORDERCARE	New IT T2
ORDERCARE	New IT T1
OPSYSWEB	Legacy T1
CFM	New IT T2
CFM	New IT T3
ORDERCARE	New IT T2


ORDERCARE	New IT T4
ORDERCARE	New IT T2
ORDERCARE	New IT T2
ORDERCARE	New IT T3
ORDERCARE	New IT T2
ORDERCARE	New IT T2
ORDERCARE	New IT T1
ORDERCARE	Legacy T1
ORDERCARE	New IT T2
INLAB	Legacy T1
ORDERCARE	New IT T1

Thanks yousef. hope to find a solution here

Re: Creating a new field from multiple tables

YoussefBelloum — Tue, 17 Jul 2018 09:11:27 GMT

here is your problem:

if(IsAutoCap =1 AND ActivitySDTier =3 AND [Catégorie H2] ='NEW IT T2','YES','NO') as Is_AutoCap_NewIt2_3

the 'NEW' values you have on your table is in lowercase, like this: New IT T2

so you will need this:

if(IsAutoCap =1 AND ActivitySDTier =3 AND [Catégorie H2] ='New IT T2','YES','NO') as Is_AutoCap_NewIt2_3

Re: Creating a new field from multiple tables

momofiore — Tue, 17 Jul 2018 09:53:20 GMT

thank so much youssefbelloum yes Now it shows the 2 choices 'YES' and 'NO'

but it show some errors on display. i mean even if a row has IsAutoCap =1 AND ActivitySDTier =3 AND [Catégorie H2] ='NEW IT T2' it show 'NO' as Is_AutoCap_NewIt2_3 the or opposite!

you think i can make a better condition? thanks Youssef .if you still have any suggestion i 'll be so thankful.

Re: Creating a new field from multiple tables

momofiore — Tue, 17 Jul 2018 09:56:40 GMT

youssefbelloum‌ here is a picture for the visualization.pay attencion to th efield Ticket sometimes it is duplicated

Re: Creating a new field from multiple tables

YoussefBelloum — Tue, 17 Jul 2018 09:58:25 GMT

using sample data you provided above, it works perfectly fine for me..

you want maybe to add more rows ? or share sample app (will be easier to test) on which the issue is still not resolved ?

Re: Creating a new field from multiple tables

YoussefBelloum — Tue, 17 Jul 2018 10:02:43 GMT

even here I think it is good, on the first two lines the ActivitySDTier is null and the condition is the value 3..

Re: Creating a new field from multiple tables

momofiore — Tue, 17 Jul 2018 10:44:53 GMT

thanks youssefbelloum‌ . but you can see in the rows 8,9,10 ActivitySDTier is null but even it shows YES .

here is a picture for a list box and combinations

when i filter with 'YES' --> mistakes in row 1,2 and 4

and here is when i filter with 'NO' --> mistake in rows 3

thanks again

Re: Creating a new field from multiple tables

momofiore — Tue, 17 Jul 2018 10:47:48 GMT

if you think my script to make temporaire table with left join is not correct you can suggest an other way to create Is_AutoCap_NewIt2_3 from the 3 tables. thanks

Re: Creating a new field from multiple tables

momofiore — Tue, 17 Jul 2018 11:07:41 GMT

heeey youssefbelloum‌ i add some samples.

you can take what you want from this table it contain all column from the 3 tables

thanks again for your time

Re: Creating a new field from multiple tables

momofiore — Tue, 17 Jul 2018 14:18:32 GMT

youssefbelloum‌ have you recieved the data?

Re: Creating a new field from multiple tables

YoussefBelloum — Tue, 17 Jul 2018 14:34:39 GMT

take a look, it seems that is working fine also here