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: 
microwin88x
Creator III
Creator III

Header/Detail Join + Sum

Hello,

I have different ID_TICKETs (in TKT_HEADER table) with multiple ID_OPERATIONs (in TKT_DETAIL table).

I need to join both tables, in order to get the following fields on a single table:

ID_TICKET / ID_MALL / PERIOD / FLAG_PAY / STAY_TIME / FLAG_AMOUNT / AMOUNT (without ID_OPERATION)

So I'd have to SUM the STAY_TIME and the AMOUNT for every ID_TICKET, not having all the detail from ID_OPERATIONs.

TKT_HEADER

ID_TICKETID_MALLPERIODFLAG_PAYSTAY_TIME
TK0132014070137
TK02320140803028
TK03320150802948
TK0432015110421
TK0532015120133
TK0632016020287
TK0732016040113
TK0852015090659

TKT_DETAIL

ID_TICKETID_OPERATIONFLAG_AMOUNTAMOUNT
TK01OP0100
TK01OP0200
TK01OP03110,74
TK02OP0400
TK02OP051247,93
TK02OP06112,4
TK03OP071148,76
TK03OP08129,75
TK03OP091119,01
TK04OP10129,75
TK04OP11184,3
TK04OP12134,71
TK05OP1300
TK05OP1400
TK05OP15134,71
TK06OP1600
TK06OP1700
TK06OP18134,71
TK07OP1900
TK07OP2000
TK07OP2100
TK08OP2200
TK08OP2300
TK08OP2400
TK08OP2512,48

The thing is when I try to join both tables by ID_TICKET, I get the following (with STAY_TIME value multiple times):

ID_TICKETID_MALLPERIODFLAG_PAYSTAY_TIMEID_OPERATIONFLAG_AMOUNTAMOUNT
TK0132014070137OP0100
TK0132014070137OP0200
TK0132014070137OP03110,74
TK02320140803028OP0400
TK02320140803028OP051247,93
TK02320140803028OP06112,4
TK03320150802948OP071148,76
TK03320150802948OP08129,75
TK03320150802948OP091119,01
TK0432015110421OP10129,75
TK0432015110421OP11184,3
TK0432015110421OP12134,71
TK0532015120133OP1300
TK0532015120133OP1400
TK0532015120133OP15134,71
TK0632016020287OP1600
TK0632016020287OP1700
TK0632016020287OP18134,71
TK0732016040113OP1900
TK0732016040113OP2000
TK0732016040113OP2100
TK0852015090659OP2200
TK0852015090659OP2300
TK0852015090659OP2400
TK0852015090659OP2512,48

And finally when I summarize the values I have something like this:

ID_TICKETID_MALLPERIODFLAG_PAYFLAG_AMOUNTSTAY_TIMEAMOUNT
TK013201407002740
TK0132014070113710,74
TK0232014080030280
TK023201408016056260,33
TK033201508018844297,52
TK043201511011263148,76
TK053201512002660
TK0532015120113334,71
TK063201602005740
TK0632016020128734,71
TK073201604003390
TK0852015090019770
TK085201509016592,48

Do you know how could I solve this, to have something like the last table but with the right STAY_TIME and AMOUNT for each ID_TICKET?

Thank you!!!

2 Replies
YoussefBelloum
Champion
Champion

Hi,

you have different ID_operation for every line

different ID_operation.png

that's why it's working with a single STAY_TIME on the last table, because you removed the ID_OPERATION

YoussefBelloum
Champion
Champion

I don't think it is about joining or not, I think it is about keeping the ID_OPERATION column on the chart or not..