Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Not applicable
‎2017-03-06 11:19 PM

output column

Hi,

I have a issue with out put column, can you suggest me the mistake in the below expression.

Load AddressNumber,

balance,

netbalance,

if(previous(AddressNumber)<>AddressNumber,Balance,

peek(OPBalance)-netbalance) as OPBalance

from AAA;


In this above expression Peek(OPBalance) is giving zero values.

I tried Peek('OPBalance') like this also.

Thanks,

765 Views
0 Likes
9 Replies
Anil_Babu_Samineni
MVP
MVP
‎2017-03-07 12:00 AM

It should work

if(previous(AddressNumber)<>AddressNumber,Balance, peek('OPBalance')-netbalance) as OPBalance

May be DM is not associated with netbalance with together

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
666 Views
0 Likes
Not applicable
‎2017-03-07 12:50 AM
Author

In response to Anil_Babu_Samineni

Hi Anil,

Datamodel is working fine. all values are tested. I found the problem is peek('OPBalance').

Value simply zero.

666 Views
0 Likes
Anil_Babu_Samineni
MVP
MVP
‎2017-03-07 12:52 AM

In response to

Can you send data please, We need to look where this is not working?

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
666 Views
0 Likes
tyagishaila
Specialist
Specialist
‎2017-03-07 12:54 AM

can you share sample data?

have you used orderby ?

666 Views
0 Likes
Not applicable
‎2017-03-07 01:39 AM
Author

In response to tyagishaila

yes, I used order by all columns asc and monthyearcolumn desc.

666 Views
0 Likes
sushil353
Master II
Master II
‎2017-03-07 01:50 AM

In response to

Hi,


I think you need to track the first record.. as previous of AddressNumber at first occurrence will give you null value.


Try this:


if(rowno()=1,Balance,if(previous(AddressNumber)<>AddressNumber,Balance, peek('OPBalance')-netbalance) )as OPBalance


HTH

sushil

666 Views
0 Likes
Not applicable
‎2017-03-07 03:37 AM
Author

In response to sushil353

Hi Sushil,

I used this but still not getting result.

After this column I am taking one more column Peek(OPBalance), so this giveing zero.

Thanks,

666 Views
0 Likes
Not applicable
‎2017-03-07 04:36 AM
Author

Hi,

       

CompanyBUAddressNumberYearMonthOPBalancenetbalanceBalance
040004110241020140300
040004110241020141200
040004110241020160600
040004110241020160800
040004110241020161100
0400041102410201702 0
04000411030102014010554.2945539
0400041103010201402-1376.54328786880
040004110301020140200
040004110301020140301376.543288
0400041103010201404-645.005911640
04000411030102014050645.0059116
040004110301020140600
040004110301020140764.000005813760
0400041103010201409-709.601784984-60.00054992
040004110301020140960.00054992-64.00000581
0400041103010201410-81.42303301376709.601785
0400041103010201410081.42303301
040004110301020141100
040004110301020150300
0400041103010201503 0
040004110501020141100
040004110501020150100
040004110501020150100
0400041105010201510-1.037024023640
040004110501020151000
040004110501020160301.037024024
040004110501020160300
040004110501020160400
0400041105010201605-211.0010264360
04000411050102016050211.0010264
040004110501020160600
0400041105010201609 0
0400041101037620140100
0400041101037620140200
04000411010376201403-99.63793680
04000411010376201404099.6379368
0400041101037620140400
04000411010376201405-115.7079570440
040004110103762014050115.707957
0400041101037620140600
0400041101037620140700
04000411010376201408 0
0400041101037720140100
0400041101037720140300
0400041101037720140400
04000411010377201412-306.264150
040004110103772015030306.26415
0400041101037720150400
0400041101037720150700
0400041101037720150900
0400041101037720160700
0400041101037720161100
04000411010377201702-65.3363520
04000411010377201702065.336352
0400041101037720170365.336352 65.336352
0400041101037820140200
04000411010378201403-644.65200640
040004110103782014040644.6520064
0400041101037820140400
0400041101037820150700
0400041101037820150900
04000411010378201510 0
04000411010379201402-5716.93080
040004110103792015085716.93085716.9308
040004110103792015090-5716.9308
0400041101037920160200
04000411010379201604-45247.73775580
04000411010379201609 45247.73776
0400041101038020140101415.62096
0400041101038020140800
0400041101038020150300
04000411010380201504-22416.90237120
04000411010380201508022416.90237
04000411010380201511-1143.386160
0400041101038020151201143.38616
0400041101038020160500
04000411010380201610-1878.420120
0400041101038020161001878.42012
04000411010380201611 0
0400041101038120140300
04000411010381201404-6179.729960
040004110103812014082286.772326179.72996
040004110103812014093892.95764-2286.77232
040004110103812014110-3892.95764
0400041101038120150200
0400041101038120150400
04000411010381201505-74.864570
04000411010381201509-4780.987557674.86457
040004110103812015124780.98755764780.987558
040004110103812016010-4780.987558
0400041101038120160200
04000411010381201603-204.17610
04000411010381201607204.1761204.1761
040004110103812016080-204.1761
0400041101038120161000
04000411010381201612 0
04000411010383201401-408.35220
0400041101038320140100
04000411010383201402-48702.80572408.3522
04000411010383201403-3252.389155648702.80572
040004110103832014046673.29165243252.389156
040004110103832014050-6673.291652
04000411010383201407-19018.3231280
040004110103832014102173.250408419018.32313
0400041101038320141113309.0149024-2173.250408
04000411010383201412-6606.0496568-13309.0149
040004110103832015012171.61699966606.049657
04000411010383201502-3866.6678164701-2171.617
0400041101038320150214738.24760243866.667816
04000411010383201503-906.541884-14738.2476
0400041101038320150410837.5312706906.541884
040004110103832015053247.761164-10837.53127
040004110103832015061537.1737982-3247.761164
040004110103832015075920.8346652-1537.173798
04000411010383201508-1261.808298-5920.834665
04000411010383201509-203.90386521261.808298
04000411010383201511-980.04528203.9038652
04000411010383201512-936.487712980.04528
04000411010383201601-591.8384552936.487712
040004110103832016025983.1764344591.8384552
040004110103832016030-5983.176434
0400041101038320160400
0400041101038320160500
04000411010383201607 0
0400041101038420140100
04000411010384201404-705.90483640
0400041101038420140519.6009056705.9048364
040004110103842014070-19.6009056
0400041101038420141100
0400041101038420151100
0400041101038420160100
0400041101038420160300
0400041101038420160500
0400041101038420160800
04000411010384201610 0
040004110103852017032060.8174362060.8174362060.817436
04000411010385201702-2060.8174360
0400041101038520170101378.923709
04000411010385201612-1378.923708960
0400041101038520161100
0400041101038520161000
0400041101038520160900
0400041101038520160700
0400041101038520160600
0400041101038520160500
0400041101038520160300
0400041101038520160100
0400041101038520151200
04000411010385201511-287.207714925.59832
040004110103852015110287.207714
04000411010385201509-925.598320
0400041101038520150800
0400041101038520150700
0400041101038520150300
0400041101038520150100
0400041101038520140700
040004110103852014020-7426.565344
040004110103852014017426.5653440
0400041101038520131207426.565344
0400041101038820131107078.1048
0400041101038820140100
040004110103882014027078.10480
04000411010388201404-5662.48384-7078.1048
04000411010388201408-91876.5226525662.48384
0400041101038820140911148.0150691876.52265
0400041101038820141184975.370472-11148.01506
040004110103882014121363.3518784

-84975.37047

Please find the sample data
666 Views
0 Likes
Not applicable
‎2017-03-07 05:29 AM
Author

In response to tyagishaila

Hi,

      

CompanyBUAddressNumberYearMonthOPBalancenetbalanceBalance
040004110241020140300
040004110241020141200
040004110241020160600
040004110241020160800
040004110241020161100
04000411024102017020
04000411030102014010554.2945539
0400041103010201402-1376.54328786880
040004110301020140200
040004110301020140301376.543288
0400041103010201404-645.005911640
04000411030102014050645.0059116
040004110301020140600
040004110301020140764.000005813760
0400041103010201409-709.601784984-60.00054992
040004110301020140960.00054992-64.00000581
0400041103010201410-81.42303301376709.601785
0400041103010201410081.42303301
040004110301020141100
040004110301020150300
04000411030102015030
040004110501020141100
040004110501020150100
040004110501020150100
0400041105010201510-1.037024023640
040004110501020151000
040004110501020160301.037024024
040004110501020160300
040004110501020160400
0400041105010201605-211.0010264360
04000411050102016050211.0010264
040004110501020160600
04000411050102016090
0400041101037620140100
0400041101037620140200
04000411010376201403-99.63793680
04000411010376201404099.6379368
0400041101037620140400
04000411010376201405-115.7079570440
040004110103762014050115.707957
0400041101037620140600
0400041101037620140700
040004110103762014080
0400041101037720140100
0400041101037720140300
0400041101037720140400
04000411010377201412-306.264150
040004110103772015030306.26415
0400041101037720150400
0400041101037720150700
0400041101037720150900
0400041101037720160700
0400041101037720161100
04000411010377201702-65.3363520
04000411010377201702065.336352
0400041101037720170365.33635265.336352
0400041101037820140200
04000411010378201403-644.65200640
040004110103782014040644.6520064
0400041101037820140400
0400041101037820150700
0400041101037820150900
040004110103782015100
04000411010379201402-5716.93080
040004110103792015085716.93085716.9308
040004110103792015090-5716.9308
0400041101037920160200
04000411010379201604-45247.73775580
0400041101037920160945247.73776
0400041101038020140101415.62096
0400041101038020140800
0400041101038020150300
04000411010380201504-22416.90237120
04000411010380201508022416.90237
04000411010380201511-1143.386160
0400041101038020151201143.38616
0400041101038020160500
04000411010380201610-1878.420120
0400041101038020161001878.42012
040004110103802016110
0400041101038120140300
04000411010381201404-6179.729960
040004110103812014082286.772326179.72996
040004110103812014093892.95764-2286.77232
040004110103812014110-3892.95764
0400041101038120150200
0400041101038120150400
04000411010381201505-74.864570
04000411010381201509-4780.987557674.86457
040004110103812015124780.98755764780.987558
040004110103812016010-4780.987558
0400041101038120160200
04000411010381201603-204.17610
04000411010381201607204.1761204.1761
040004110103812016080-204.1761
0400041101038120161000
040004110103812016120
04000411010383201401-408.35220
0400041101038320140100
04000411010383201402-48702.80572408.3522
04000411010383201403-3252.389155648702.80572
040004110103832014046673.29165243252.389156
040004110103832014050-6673.291652
04000411010383201407-19018.3231280
040004110103832014102173.250408419018.32313
0400041101038320141113309.0149024-2173.250408
04000411010383201412-6606.0496568-13309.0149
040004110103832015012171.61699966606.049657
04000411010383201502-3866.6678164701-2171.617
0400041101038320150214738.24760243866.667816
04000411010383201503-906.541884-14738.2476
0400041101038320150410837.5312706906.541884
040004110103832015053247.761164-10837.53127
040004110103832015061537.1737982-3247.761164
040004110103832015075920.8346652-1537.173798
04000411010383201508-1261.808298-5920.834665
04000411010383201509-203.90386521261.808298
04000411010383201511-980.04528203.9038652
04000411010383201512-936.487712980.04528
04000411010383201601-591.8384552936.487712
040004110103832016025983.1764344591.8384552
040004110103832016030-5983.176434
0400041101038320160400
0400041101038320160500
040004110103832016070
0400041101038420140100
04000411010384201404-705.90483640
0400041101038420140519.6009056705.9048364
040004110103842014070-19.6009056
0400041101038420141100
0400041101038420151100
0400041101038420160100
0400041101038420160300
0400041101038420160500
0400041101038420160800
040004110103842016100
040004110103852017032060.8174362060.8174362060.817436
04000411010385201702-2060.8174360
0400041101038520170101378.923709
04000411010385201612-1378.923708960
0400041101038520161100
0400041101038520161000
0400041101038520160900
0400041101038520160700
0400041101038520160600
0400041101038520160500
0400041101038520160300
0400041101038520160100
0400041101038520151200
04000411010385201511-287.207714925.59832
040004110103852015110287.207714
04000411010385201509-925.598320
0400041101038520150800
0400041101038520150700
0400041101038520150300
0400041101038520150100
0400041101038520140700
040004110103852014020-7426.565344
040004110103852014017426.5653440
0400041101038520131207426.565344
0400041101038820131107078.1048
0400041101038820140100
040004110103882014027078.10480
04000411010388201404-5662.48384-7078.1048
04000411010388201408-91876.5226525662.48384
0400041101038820140911148.0150691876.52265
0400041101038820141184975.370472-11148.01506

Please find the sample data, if you find any thing update me

666 Views
0 Likes