topic Re: no data replace 0 in QlikView

no data replace 0

Fri, 18 Apr 2014 04:45:58 GMT

if there is no data end set the value to zero.I need the hellp pn this for a calculation.

if net balance dnt ave data then we should get 0 so tha in comupaion of

balance-net balance we get, blance-0=balance

Regards,

Prajna

Re: no data replace 0

anbu1984 — Fri, 18 Apr 2014 04:54:17 GMT

Can you explain with your data?

Re: no data replace 0

Fri, 18 Apr 2014 05:04:46 GMT

Hi,

Actually i have creaed a master date field.. i have a table wih few dates and balance netbalance

onemore table with balance.

so i need to subtract net balance from balance for all date fields.

but there are few data or net balnce.

Id date netbalance

1 13 apr 48

2 14 apr 79

3 17 apr 90

Balance:

id balance

1 400

2 500\

3 788

so now i need to subtract netbalance from balance

id date total

1 13 apr 400-48

1 14 apr 400-79

1 15apr 400-0

1 16apr 400-0

1 17apr 400-90

canu help me out..

Regards,

Prajna

Re: no data replace 0

anbu1984 — Fri, 18 Apr 2014 05:15:54 GMT

Can you explain your logic? Do you want to create rows with missing dates? Do you want to join with only id 1 in balance table or all ids like below?

id date total

1 13 apr 400-48

1 14 apr 400-79

1 15apr 400-0

1 16apr 400-0

1 17apr 400-90

2 13 apr 500-48

2 14 apr 500-79

2 15apr 500-0

2 16apr 500-0

2 17apr 500-90

3 .....

Re: no data replace 0

Fri, 18 Apr 2014 05:19:52 GMT

for all id's for all dates.. JAN-DEC if i dnt have records for netbalance then it will 0 so when i subtract balance wih net balance and f i dnt jave value for netbalance then it will be set to 0and

i will get balance.

Re: no data replace 0

Fri, 18 Apr 2014 05:33:50 GMT

Hi Prajna,

First you have to Join Calander table with ex table so that you get all dates in ex:. Once you get all dates you can use it with balance table.

Kindly tell if this helped ?

Thanks & Regards

Suhas Gupta

Re: no data replace 0

MK_QSL — Fri, 18 Apr 2014 06:14:39 GMT

Can you provide little more data... Say for three ID and 12 Months for all ID

Re: no data replace 0

Fri, 18 Apr 2014 06:21:00 GMT

its already link.. the prob is i get only related data if data is not there for date field then i should get 0

so that i should have data

Regards,

Prajna

Re: no data replace 0

Fri, 18 Apr 2014 06:34:33 GMT

Hi,

My requirement is i have few transactions posted for few dates.but i need to show for all dates so i used master date

linking to the transaction date.

I have to do calculation

subtract transaction net amount with balnce amount

balacne amt is from other table

transaction n dbalance tanle is linked with id.

if there is no transaction data for particular date then i need to get 0.

Regards,

Prajna

Re: no data replace 0

MK_QSL — Fri, 18 Apr 2014 06:36:30 GMT

Understood your point of view, but above sample data file is showing cross join which is looking of no logic.

If you can provide some proper sample data, will definitely work out.

Re: no data replace 0

Fri, 18 Apr 2014 06:43:55 GMT

table 1:(Transaction table)

ID Date Netbalance

1 12 apr 50

1 13apr 89

1 16apr 56

1 17apr 78

2 18apr 90

1 19APR 98

2 20apr 89

table 2:(BALANCE)

ID Balance

1 450

2 79

Both table are linked by ID ,ID in balance table is PK

Whereas FK in transaction table

Re: no data replace 0

MK_QSL — Fri, 18 Apr 2014 06:55:16 GMT

Let me go to some more depth....

Here for ID = 1

Balance = 50

If you want Total

ID Date Netbalance TOTAL1 TOTAL2

1 12apr 50 450-50 450-50

1 13apr 89 450-89 450-50-89

1 16apr 56 450-56 450-50-89-56

1 17apr 78

1 19apr 98

Which TOTAL you want? 1 or 2?

Re: no data replace 0

Fri, 18 Apr 2014 08:15:19 GMT

okay i will tell u exactly what i want..which bit complex

u have 2 tables transacion and balance

transaction for everyday activity and balace is recorded at the end of day.

transaction table : we have date ,balance id,cresdits,debits and net

balance table we have : balance id,balance amount

transaction table is

ID Date Netbalance

1 12 apr 50

1 13apr 89

1 16apr 56

1 17apr 78

2 18apr 90

1 19APR 98

2 20apr 89

balance table has one record for each balacne id.it always hold currenct balance

my req is to show total balance for all dates.if there is no transaction then i need to find what is he possible balance for that particular day.

now question is how will i find balance on that day?

i need to collect balance amt frombalance table that would be for currenct day and subtract with net balance to obtain my balnce on previous day.

Ex: balance credits debits net

if i have credited 100 on 12 th apr then,

100 100 - 100

if credited 200,13apr

300 200 - 200

if debited 100,15 apr

Bal cre deb net

200 - 100 -100

but in actual table balance value willl be over ridden

we have only 200in balance and there is no date field

if i want to knw what was the balnce on 14 apr

then i need to take balance amount from balance table that would be for currenc date say 18th apr

200 in example

subtract that witrh 18th apr net..as we dnt have record it should be 0 for 18th

so balance on 18 is 200-0=200

then on 17th,200-0=200

on 16th 200-0=200

on 15th 200-(-100)=300

on 14th its 300-0=300 so we go the balance on 14 th apr.

This is what i wanted.Hope its clear.

Can u help me further on this

Thank you

Regards,

Prajna

Re: no data replace 0

manojkulkarni — Fri, 18 Apr 2014 09:34:07 GMT

go thru this link http://community.qlik.com/docs/DOC-3786

you can achieve the solution by creating missing values.

Re: no data replace 0

MK_QSL — Fri, 18 Apr 2014 09:41:51 GMT

I am sorry but it's difficult to work without data....

Can you provide sample data in excel file? This is necessary to get answer quickly as it's difficult to create dummy data for such questions.

Re: no data replace 0

Fri, 18 Apr 2014 10:06:04 GMT

hi,

I have attached some sample data.Waiting for your solution and suggestions:)

Regards,

Prajna

Re: no data replace 0

MK_QSL — Fri, 18 Apr 2014 10:28:41 GMT

Not Clear... can you provide your final result table in excel file based on the two excel files you have provided.

Re: no data replace 0

Fri, 18 Apr 2014 10:31:34 GMT

Hi,

There is no result table.from the two tables i need to showcase in dashboard.

Total balance for each day.

Is it possible?

Regards,

Prajna

Re: no data replace 0

Fri, 18 Apr 2014 11:27:09 GMT

Hi Prajna,

Try Below :

Calander Table :

//Contains all dates i.e. all 365 days of year.

C_NB Alias_ID Date

0 111 01.01.2000

0 111 02.01.2000

0 111 03.01.2000

....

;

Balance:

Alias_ID ID Balance

111 1 400

111 2 500

111 3 600

...

;

Transaction:

ID Date NB

1 01.01.2000 15

3 05.01.2000 18

...

;

Now in the Table Chart Take ID from Balance Table and Date from Calander Table as dimension

in Expression use =Balance - if(NB = '', C_NB,NB)

I hope this will solve your problem. I havn't tried request you to kindly try and let us know if this works.

Thanks & Regards

Suhas Gupta

Re: no data replace 0

Mon, 21 Apr 2014 10:51:26 GMT

Hi,

Howw to generate alias id in calender?

and is it feasivble to alter table structure by adding a new column to existing table balance?

Thank you

Regards,

Prajna