topic Table Chart expression in QlikView

Table Chart expression

inam — Tue, 23 May 2023 10:34:04 GMT

Hello All,

I want to add one column in my existing table but I am getting error.

with the help of below sheet i need to add Total column in my table

sheet:-

My Existing Table :- below is my existing table here i want to add one more column sales Budget (Total).

Expected Output:-

for exmaple

Thailand :- it is destination (unit) column

source:- B2B & XML

B2B is 18,00,000 & XML is 20,30,000 Total is 38,30,000 so i wnat to add Total as ( sales Budget ) in My table.

Re: Table Chart expression

PrashantSangle — Tue, 23 May 2023 14:15:43 GMT

Can you provide some more info about your query?

1: how you load that file qliksense? sharing your script will be helpful.

2: What are the expression you used?

3: What are the things you tried? what is the error you are getting?

regards,

Prashant Sangle

Re: Table Chart expression

inam — Wed, 24 May 2023 06:29:41 GMT

how you load that file Qliksense? sharing your script will be helpful.

:- I have 2 sheets 1 is below

LOAD No.,
[Agent Name],
[Agent Country],
[Agent City],
[Bkg Type],
[File No.],
[O/S Ref.],
[Destination Country],
UNIT,
[Destination City],
[Hotel/Transport/Place],
[Room Type/Service Name],
[Meal Plan],
[Guest Name],
Nationality,
[Total Pax],
[Service Status],
[Booking Status],
[Check-In],
Month,
[Check-out],
[No. Of Units],
[No. Of Nights],
[Room Nights],
[Invoice Currency],
Invoice,
[Invoice Default Currency],
[Cost Currency],
Cost,
[Cost Default Currency],
[Source of booking],
[Bkg Source],
[Handled By],
[Time of Create Service Booking],
[User Create],
[Username Create],
Supplier,
Margin,
margin%,
[AT Units],
[Actual Sales],
[Actual Cost],
[Actual Margin],
[Actual Margin %],
[Supplier Source],
Bookings,
[Contribution on Sales],
[Sales (Budget)],
[AT Units1],
AU,
AV
FROM

and 2nd sheet is (budget sheet i share on the top)

LOAD Destination,
Source,
JAN,
FEB,
MAR,
APR,
MAY,
JUN,
JUL,
AUG,
SEP,
OCT,
NOV,
DEC,
TOTAL

What are the expression you used?

:- in expression I add normally TOTAL(Present in budget sheet) but its not getting any answer when i add TOTAL in expression the total pax and all other field total amount is changed.

What are the things you tried? what is the error you are getting?

:- 1st i load both sheet using left join and in expression tab add only TOTAL but not getting any answer.

you have any solution how i add Sales Budget column in my existing table? i need expression.

Re: Table Chart expression

PrashantSangle — Wed, 24 May 2023 06:42:33 GMT

in your 1st table you have Unit field which contain your destination address. So, you have to rename the field Destination from 2nd table

so your 2nd table will became

LOAD Destination as Unit,
Source,
JAN,
FEB,
MAR,
APR,
MAY,
JUN,
JUL,
AUG,
SEP,
OCT,
NOV,
DEC,
TOTAL

Then in your expression just use sum(TOTAL)

Regards,

Prashant Sangle

Re: Table Chart expression

inam — Wed, 24 May 2023 07:32:49 GMT

Hii Prashant Thank for reply .

i apply the same but getting answer is Different also why Total Pax and all others field total values is changed?

below is snapshot.

i load this below:-

temp:
LOAD No.,
[Agent Name],
[Agent Country],
[Agent City],
[Bkg Type],
[File No.],
[O/S Ref.],
[Destination Country],
UNIT,
[Destination City],
[Hotel/Transport/Place],
[Room Type/Service Name],
[Meal Plan],
[Guest Name],
Nationality,
[Total Pax],
[Service Status],
[Booking Status],
[Check-In],
Month,
[Check-out],
[No. Of Units],
[No. Of Nights],
[Room Nights],
[Invoice Currency],
Invoice,
[Invoice Default Currency],
[Cost Currency],
Cost,
[Cost Default Currency],
[Source of booking],
[Bkg Source],
[Handled By],
[Time of Create Service Booking],
[User Create],
[Username Create],
Supplier,
Margin,
margin%,
[AT Units],
[Actual Sales],
[Actual Cost],
[Actual Margin],
[Actual Margin %],
[Supplier Source],
Bookings,
[Contribution on Sales],
[Sales (Budget)],
[AT Units1],
AU,
AV
FROM
[D:\QlikView_inam\data\At Group\vi. B2B Online Reports & vii. Sourcing Report\DIFFRENT\AT Group Online Booking Report.qvd](qvd);

Left join(temp)
LOAD Destination as UNIT,
Source,
JAN,
FEB,
MAR,
APR,
MAY,
JUN,
JUL,
AUG,
SEP,
OCT,
NOV,
DEC,
TOTAL AS Total2
FROM
[D:\QlikView_inam\data\At Group\vi. B2B Online Reports & vii. Sourcing Report\DIFFRENT\budget.xlsx]
(ooxml, embedded labels, table is Sheet1);

Re: Table Chart expression

MayilVahanan — Wed, 24 May 2023 07:44:56 GMT

I think, your budget excel sheet has duplicate values or its for 2 year? What is the link between 2 tables?

Instead of join, if you keep separately, 2 tables are join auto based on common field [Key field], hope it doesn't change "Total" values

Re: Table Chart expression

inam — Wed, 24 May 2023 10:12:42 GMT

Hello ,

Thanks MayilVahanan working fine now😊

i have one another doubt can you please help.

I need this Excel formula in QlikView.

excel formula :- =IFERROR(1/COUNTIF([File No.],[@[File No.]]),0)

I am trying this IF(COUNT([File No.]) > 0, 1 / COUNT({< [File No.] = {"$(= [File No.])"} >}), 0) but its not work.

Re: Table Chart expression

Chanty4u — Wed, 24 May 2023 10:14:04 GMT

Try this

=If(Count([File No.]) > 0, 1 / CountDistinct({< [File No.] = {"$(= [File No.])"} >} [File No.]), 0)

Re: Table Chart expression

inam — Wed, 24 May 2023 11:08:16 GMT

Hello

I apply but getting error. how I resolve this .