Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
momofiore
Creator
Creator

Creating a new field from multiple tables

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

1 Solution

Accepted Solutions
YoussefBelloum
Champion
Champion

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


View solution in original post

14 Replies
YoussefBelloum
Champion
Champion

Hi,

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


momofiore
Creator
Creator
Author

there is data yet in my table

m3.PNG

YoussefBelloum
Champion
Champion

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

momofiore
Creator
Creator
Author

Hey youssefbelloum

yes sure. table FactTicket:

 

%TicketKeyIsAutoCapCloseDate%CIApplicationName
876552042096,3691ONROUTE
87656241732,6601ORDERCARE
876568141732,6601ORDERCARE
87704241655,5573OPSYSWEB
877095041655,5573CFM
87709542124,395CFM
87716541726,6452ORDERCARE
877974141794,618
87797441660,6293
879242041772,7227ORDERCARE
880533ORDERCARE
882369142285,6157ORDERCARE
88301042285,6157ORDERCARE
883319141772,7198ORDERCARE
88389341772,7187ORDERCARE
883919141892,3912ORDERCARE
885891041956,7346ORDERCARE
889089042285,6305ORDERCARE
88938742144,3816INLAB
889415142144,3349

ORDERCARE

table FactTicketActivity:

 

%TicketKeyActivitySDTierGroup
0FR_EUR_BUILD/DEV_OM
8765623FR_EUR_BUILD/CONVERGENCE
8765683FR_EUR_BUILD/OM
8770423FR_EUR_BUILD/OPW_OPSYSWEB
8770953FR_EUR_BUILD/CONVERGENCE
877095FR_EUR_BUILD/CONVERGENCE
8771653FR_EUR_BUILD/CONVERGENCE_BENO
8779742FR_EUR_BUILD/FIN_CFM
877974FR_EUR_BUILD/FIN_CFM
8792423FR_EUR_BUILD/CONVERGENCE
8805331FR_EUR_BUILD/CONVERGENCE
8823693FR_EUR_BUILD/CONVERGENCE
8830103FR_EUR_BUILD/CONVERGENCE
8833192FR_EUR_BUILD/OM
8838933FR_EUR_BUILD/CONVERGENCE
8839190FR_EUR_BUILD/CONVERGENCE
8858913FR_EUR_BUILD/OM
8890891FR_EUR_BUILD/OM
8893873FR_EUR_BUILD/LAB.MGT
8894152FR_EUR_BUILD/EDI

table Application:

 

%CIApplicationNameCatégorie H2
ONROUTENew IT T2
ORDERCARENew IT T2
ORDERCARENew IT T1
OPSYSWEBLegacy T1
CFMNew IT T2
CFMNew IT T3
ORDERCARENew IT T2
ORDERCARENew IT T4
ORDERCARENew IT T2
ORDERCARENew IT T2
ORDERCARENew IT T3
ORDERCARENew IT T2
ORDERCARENew IT T2
ORDERCARENew IT T1
ORDERCARELegacy T1
ORDERCARENew IT T2
INLABLegacy T1
ORDERCARENew IT T1

Thanks yousef. hope to find a solution here

YoussefBelloum
Champion
Champion

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
Creator
Creator
Author

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
Creator
Creator
Author

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

error.PNG

YoussefBelloum
Champion
Champion

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
Champion
Champion

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