QlikView App Dev

[momofiore]

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‌

[YoussefBelloum]

Hi,

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

[momofiore]

there is data yet in my table

[YoussefBelloum]

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

[momofiore]

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

[YoussefBelloum · Accepted solution]

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

[momofiore]

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.

[momofiore]

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

[YoussefBelloum]

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 ?

[YoussefBelloum]

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