Skip to main content

Qlik

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: 
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: 
microwin88x
Creator III
Creator III
‎2017-05-05 04:44 PM

Create a field with Last Year's value

Hi,

I have the following table:

- CUSTOMER: It's the Customer ID.

- CONTRACT: Contract ID for Customer.

- UNIT: Physical Location for that Customer.

- PERIOD: Period for Transaction.

- AMOUNT: Amount for Transaction.

CUSTOMERCONTRACTUNITYEARMONTHPERIODAMOUNT
300033312031LO0762014jan2014016974
300033312031LO0762014feb2014028153
300033312031LO0762014mar2014037967
300033312031LO0762014apr2014048118
300033312031LO0762014may2014058985
300033312031LO0762014jun20140610820
300033312031LO0762014jul20140710781
300033312031LO0762014aug20140810361
300033312031LO0762014sep2014098889
300033312031LO0762014oct2014108305
300033312031LO0762014nov20141110417
300033312031LO0762014dec20141217244
300033312031LO0762015jan20150113458
300033312031LO0762015feb2015029792
300033312031LO0762015mar20150313863
300033312031LO0762015apr20150414480
300033312031LO0762015may20150514800
300033312031LO0762015jun20150616356
300033312031LO0762015jul20150720282
300033312031LO0762015aug20150820507
300033321098LO0762015sep2015091348
300033312031LO0762015sep20150910242
300033312031LO0762015oct20151014399
300033312031LO0762015nov20151115975
300033312031LO0762015dec20151216541
300033312031LO0762016jan20160111211
300033312031LO0762016feb20160214775
300033312031LO0762016mar20160318849
300033312031LO0762016apr20160412901
300033312031LO0762016may2016055931
300033321098LO0762016may20160510640
300033321098LO0762016jun20160614807
300033321098LO0762016jul20160715912
300033321098LO0762016aug20160814639
300033321098LO0762016sep20160916358
300033321098LO0762016oct20161018262
300033321098LO0762016nov20161111917
300033321098LO0762016dec20161216524
300033321098LO0762017jan20170118094
300033321098LO0762017feb20170217321
300033321098LO0762017mar20170315631
300033321098LO0762017apr20170417193
300033321098LO0762017may2017053155
30003777381LO0012014jan20140193852
300037717228LO0012014feb2014022325
30003777381LO0012014feb20140261317
30003777381LO0012014mar20140378619
300037717228LO0012014apr20140438471
30003777381LO0012014apr20140439083
300037717228LO0012014may20140598469
300037717228LO0012014jun201406110237
300037717228LO0012014jul20140769946
300037717228LO0012014aug20140895158
300037717228LO0012014sep20140977824
300037717228LO0012014oct201410101601
300037717228LO0012014nov20141177811
300037717228LO0012014dec201412117653
300037717228LO0012015jan20150185369
300037717228LO0012015feb20150267573
300037717228LO0012015mar20150391392
300037717228LO0012015apr20150477619
300037717228LO0012015may20150597637
300037717228LO0012015jun201506111140
300037717228LO0012015jul201507107736
300037717228LO0012015aug201508111971
300037717228LO0012015sep20150984691
300037717228LO0012015oct201510107413
300037717228LO0012015nov201511107465
300037717228LO0012015dec201512139325
300037717228LO0012016jan20160177616
300037717228LO0012016feb20160275560
300037717228LO0012016mar20160390046
300037717228LO0012016apr201604102882
300037717228LO0012016may201605103817
300037717228LO0012016jun201606118126
300037717228LO0012016jul201607127735
300037717228LO0012016aug20160899631
300037717228LO0012016sep20160986894
300037717228LO0012016oct201610115902
300037717228LO0012016nov20161184059
300037717228LO0012016dec201612136959
300037717228LO0012017jan201701119856
300037717228LO0012017feb20170286674
300037717228LO0012017mar20170396927
300037717228LO0012017apr201704104974
300037717228LO0012017may20170511343
300039015155LO0592014jan2014015302
300039015155LO0592014feb2014027583
300039015155LO0592014mar2014035272
300039015155LO0592014apr2014045000
300039015155LO0592014may2014054445
300039015155LO0592014jun2014066297
300039015155LO0592014jul2014076633
300039015155LO0592014aug2014086925
300039015155LO0592014sep2014096683
300039015155LO0592014oct20141011084
300039015155LO0592014nov2014116673
300039015155LO0592014dec20141213716
300039015155LO0592015jan2015017323
300039015155LO0592015feb2015027414
300039015155LO0592015mar2015037075
300039015155LO0592015apr2015044535
300039015155LO0592015may2015054707
300039015155LO0592015jun2015064728
300039015155LO0592015jul2015076572
300039015155LO0592015aug2015088053
300039015155LO0592015sep2015098084
300039015155LO0592015oct20151011670
300039015155LO0592015nov2015116674
300039015155LO0592015dec20151217694
300039015155LO0592016jan2016017827
300039015155LO0592016feb20160210909
300039015155LO0592016mar20160312717
300039015155LO0592016apr2016047248
300039015155LO0592016may2016058891
300039015155LO0592016jun2016067717
300039022731LO0592016jul2016075795
300039015155LO0592016jul2016077173
300039022731LO0592016aug20160810487
300039022731LO0592016sep2016099374
300039022731LO0592016oct20161016361
300039022731LO0592016nov20161110804
300039022731LO0592016dec20161224861
300039022731LO0592017jan20170112224
300039022731LO0592017feb20170211260
300039022731LO0592017mar2017039259
300039022731LO0592017apr2017048822
300039022731LO0592017may2017052350
300041911405LO0582014jan2014014758
300041911405LO0582014feb2014024737
300041911405LO0582014mar2014037825
300041911405LO0582014apr2014049883
300041911405LO0582014may2014059455
300041911405LO0582014jun20140611516
300041911405LO0582014jul2014079184
300041911405LO0582014aug2014087262
300041911405LO0582014sep2014099862
300041911405LO0582014oct20141017847
300041911405LO0582014nov20141110454
300041911405LO0582014dec20141217267
300041911405LO0582015jan2015016602
300041911405LO0582015feb2015026599
300041911405LO0582015mar2015031297
300041919423LO0582015mar20150310221
300041919423LO0582015apr20150412869
300041919423LO0582015may20150513998
300041919423LO0582015jun20150610873
300041919423LO0582015jul2015079549
300041919423LO0582015aug20150810009
300041919423LO0582015sep20150914736
300041919423LO0582015oct20151020378
300041919423LO0582015nov20151112520
300041919423LO0582015dec20151223264
300041919423LO0582016jan2016017419
300041919423LO0582016feb2016027485
300041919423LO0582016mar20160313120
300041919423LO0582016apr20160414334
300041919423LO0582016may20160515509
300041919423LO0582016jun20160614698
300041919423LO0582016jul20160714876
300041919423LO0582016aug20160812356
300041919423LO0582016sep20160913574
300041919423LO0582016oct20161023320
300041919423LO0582016nov20161116664
300041919423LO0582016dec20161229297
300041919423LO0582017jan20170112086
300041919423LO0582017feb20170210899
300041919423LO0582017mar20170312277
300041919423LO0582017apr20170414683
300041919423LO0582017may2017053494

So I need to create a new field AMOUNT_LY, where I should get the AMOUNT from last year's period.

This would be the remaining result: (Note that AMOUNT_CY is the value for current year period)

Please, note that there might be multiple CONTRACTS for a CUSTOMER, as you could see.

CUSTOMERPERIODAMOUNT_CYAMOUNT_LY
30003332014016974
30003332014028153
30003332014037967
30003332014048118
30003332014058985
300033320140610820
300033320140710781
300033320140810361
30003332014098889
30003332014108305
300033320141110417
300033320141217244
3000333201501134586974
300033320150297928153
3000333201503138637967
3000333201504144808118
3000333201505148008985
30003332015061635610820
30003332015072028210781
30003332015082050710361
3000333201509115908889
3000333201510143998305
30003332015111597510417
30003332015121654117244
30003332016011121113458
3000333201602147759792
30003332016031884913863
30003332016041290114480
30003332016051657114800
30003332016061480716356
30003332016071591220282
30003332016081463920507
30003332016091635811590
30003332016101826214399
30003332016111191715975
30003332016121652416541
30003332017011809411211
30003332017021732114775
30003332017031563118849
30003332017041719312901
3000333201705315516571
300037720140193852
300037720140263642
300037720140378619
300037720140477554
300037720140598469
3000377201406110237
300037720140769946
300037720140895158
300037720140977824
3000377201410101601
300037720141177811
3000377201412117653
30003772015018536993852
30003772015026757363642
30003772015039139278619
30003772015047761977554
30003772015059763798469
3000377201506111140110237
300037720150710773669946
300037720150811197195158
30003772015098469177824
3000377201510107413101601
300037720151110746577811
3000377201512139325117653
30003772016017761685369
30003772016027556067573
30003772016039004691392
300037720160410288277619
300037720160510381797637
3000377201606118126111140
3000377201607127735107736
300037720160899631111971
30003772016098689484691
3000377201610115902107413
300037720161184059107465
3000377201612136959139325
300037720170111985677616
30003772017028667475560
30003772017039692790046
3000377201704104974102882
300037720170511343103817
30003902014015302
30003902014027583
30003902014035272
30003902014045000
30003902014054445
30003902014066297
30003902014076633
30003902014086925
30003902014096683
300039020141011084
30003902014116673
300039020141213716
300039020150173235302
300039020150274147583
300039020150370755272
300039020150445355000
300039020150547074445
300039020150647286297
300039020150765726633
300039020150880536925
300039020150980846683
30003902015101167011084
300039020151166746673
30003902015121769413716
300039020160178277323
3000390201602109097414
3000390201603127177075
300039020160472484535
300039020160588914707
300039020160677174728
3000390201607129686572
3000390201608104878053
300039020160993748084
30003902016101636111670
3000390201611108046674
30003902016122486117694
3000390201701122247827
30003902017021126010909
3000390201703925912717
300039020170488227248
300039020170523508891
30004192014014758
30004192014024737
30004192014037825
30004192014049883
30004192014059455
300041920140611516
30004192014079184
30004192014087262
30004192014099862
300041920141017847
300041920141110454
300041920141217267
300041920150166024758
300041920150265994737
3000419201503115187825
3000419201504128699883
3000419201505139989455
30004192015061087311516
300041920150795499184
3000419201508100097262
3000419201509147369862
30004192015102037817847
30004192015111252010454
30004192015122326417267
300041920160174196602
300041920160274856599
30004192016031312011518
30004192016041433412869
30004192016051550913998
30004192016061469810873
3000419201607148769549
30004192016081235610009
30004192016091357414736
30004192016102332020378
30004192016111666412520
30004192016122929723264
3000419201701120867419
3000419201702108997485
30004192017031227713120
30004192017041468314334
3000419201705349415509

Is there any way we could do this by script?

Thank you!!!

418 Views
0 Likes
2 Replies
Anonymous
Not applicable
‎2017-05-05 05:48 PM

You could try something like this.

Data:

LOAD * INLINE [ 

    CUSTOMER, CONTRACT, UNIT, YEAR, MONTH, PERIOD, AMOUNT

    3000333, 12031, LO076, 2014, jan, 201401, 6974

    3000333, 12031, LO076, 2014, feb, 201402, 8153

    3000333, 12031, LO076, 2014, mar, 201403, 7967

    3000333, 12031, LO076, 2014, apr, 201404, 8118

    3000333, 12031, LO076, 2014, may, 201405, 8985

    3000333, 12031, LO076, 2014, jun, 201406, 10820

    3000333, 12031, LO076, 2014, jul, 201407, 10781

    3000333, 12031, LO076, 2014, aug, 201408, 10361

    3000333, 12031, LO076, 2014, sep, 201409, 8889

    3000333, 12031, LO076, 2014, oct, 201410, 8305

    3000333, 12031, LO076, 2014, nov, 201411, 10417

    3000333, 12031, LO076, 2014, dec, 201412, 17244

    3000333, 12031, LO076, 2015, jan, 201501, 13458

    3000333, 12031, LO076, 2015, feb, 201502, 9792

    3000333, 12031, LO076, 2015, mar, 201503, 13863

    3000333, 12031, LO076, 2015, apr, 201504, 14480

    3000333, 12031, LO076, 2015, may, 201505, 14800

    3000333, 12031, LO076, 2015, jun, 201506, 16356

    3000333, 12031, LO076, 2015, jul, 201507, 20282

    3000333, 12031, LO076, 2015, aug, 201508, 20507

    3000333, 21098, LO076, 2015, sep, 201509, 1348

    3000333, 12031, LO076, 2015, sep, 201509, 10242

    3000333, 12031, LO076, 2015, oct, 201510, 14399

    3000333, 12031, LO076, 2015, nov, 201511, 15975

    3000333, 12031, LO076, 2015, dec, 201512, 16541

    3000333, 12031, LO076, 2016, jan, 201601, 11211

    3000333, 12031, LO076, 2016, feb, 201602, 14775

    3000333, 12031, LO076, 2016, mar, 201603, 18849

    3000333, 12031, LO076, 2016, apr, 201604, 12901

    3000333, 12031, LO076, 2016, may, 201605, 5931

    3000333, 21098, LO076, 2016, may, 201605, 10640

    3000333, 21098, LO076, 2016, jun, 201606, 14807

    3000333, 21098, LO076, 2016, jul, 201607, 15912

    3000333, 21098, LO076, 2016, aug, 201608, 14639

    3000333, 21098, LO076, 2016, sep, 201609, 16358

    3000333, 21098, LO076, 2016, oct, 201610, 18262

    3000333, 21098, LO076, 2016, nov, 201611, 11917

    3000333, 21098, LO076, 2016, dec, 201612, 16524

    3000333, 21098, LO076, 2017, jan, 201701, 18094

    3000333, 21098, LO076, 2017, feb, 201702, 17321

    3000333, 21098, LO076, 2017, mar, 201703, 15631

    3000333, 21098, LO076, 2017, apr, 201704, 17193

    3000333, 21098, LO076, 2017, may, 201705, 3155

    3000377, 7381, LO001, 2014, jan, 201401, 93852

    3000377, 17228, LO001, 2014, feb, 201402, 2325

    3000377, 7381, LO001, 2014, feb, 201402, 61317

    3000377, 7381, LO001, 2014, mar, 201403, 78619

    3000377, 17228, LO001, 2014, apr, 201404, 38471

    3000377, 7381, LO001, 2014, apr, 201404, 39083

    3000377, 17228, LO001, 2014, may, 201405, 98469

    3000377, 17228, LO001, 2014, jun, 201406, 110237

    3000377, 17228, LO001, 2014, jul, 201407, 69946

    3000377, 17228, LO001, 2014, aug, 201408, 95158

    3000377, 17228, LO001, 2014, sep, 201409, 77824

    3000377, 17228, LO001, 2014, oct, 201410, 101601

    3000377, 17228, LO001, 2014, nov, 201411, 77811

    3000377, 17228, LO001, 2014, dec, 201412, 117653

    3000377, 17228, LO001, 2015, jan, 201501, 85369

    3000377, 17228, LO001, 2015, feb, 201502, 67573

    3000377, 17228, LO001, 2015, mar, 201503, 91392

    3000377, 17228, LO001, 2015, apr, 201504, 77619

    3000377, 17228, LO001, 2015, may, 201505, 97637

    3000377, 17228, LO001, 2015, jun, 201506, 111140

    3000377, 17228, LO001, 2015, jul, 201507, 107736

    3000377, 17228, LO001, 2015, aug, 201508, 111971

    3000377, 17228, LO001, 2015, sep, 201509, 84691

    3000377, 17228, LO001, 2015, oct, 201510, 107413

    3000377, 17228, LO001, 2015, nov, 201511, 107465

    3000377, 17228, LO001, 2015, dec, 201512, 139325

    3000377, 17228, LO001, 2016, jan, 201601, 77616

    3000377, 17228, LO001, 2016, feb, 201602, 75560

    3000377, 17228, LO001, 2016, mar, 201603, 90046

    3000377, 17228, LO001, 2016, apr, 201604, 102882

    3000377, 17228, LO001, 2016, may, 201605, 103817

    3000377, 17228, LO001, 2016, jun, 201606, 118126

    3000377, 17228, LO001, 2016, jul, 201607, 127735

    3000377, 17228, LO001, 2016, aug, 201608, 99631

    3000377, 17228, LO001, 2016, sep, 201609, 86894

    3000377, 17228, LO001, 2016, oct, 201610, 115902

    3000377, 17228, LO001, 2016, nov, 201611, 84059

    3000377, 17228, LO001, 2016, dec, 201612, 136959

    3000377, 17228, LO001, 2017, jan, 201701, 119856

    3000377, 17228, LO001, 2017, feb, 201702, 86674

    3000377, 17228, LO001, 2017, mar, 201703, 96927

    3000377, 17228, LO001, 2017, apr, 201704, 104974

    3000377, 17228, LO001, 2017, may, 201705, 11343

    3000390, 15155, LO059, 2014, jan, 201401, 5302

    3000390, 15155, LO059, 2014, feb, 201402, 7583

    3000390, 15155, LO059, 2014, mar, 201403, 5272

    3000390, 15155, LO059, 2014, apr, 201404, 5000

    3000390, 15155, LO059, 2014, may, 201405, 4445

    3000390, 15155, LO059, 2014, jun, 201406, 6297

    3000390, 15155, LO059, 2014, jul, 201407, 6633

    3000390, 15155, LO059, 2014, aug, 201408, 6925

    3000390, 15155, LO059, 2014, sep, 201409, 6683

    3000390, 15155, LO059, 2014, oct, 201410, 11084

    3000390, 15155, LO059, 2014, nov, 201411, 6673

    3000390, 15155, LO059, 2014, dec, 201412, 13716

    3000390, 15155, LO059, 2015, jan, 201501, 7323

    3000390, 15155, LO059, 2015, feb, 201502, 7414

    3000390, 15155, LO059, 2015, mar, 201503, 7075

    3000390, 15155, LO059, 2015, apr, 201504, 4535

    3000390, 15155, LO059, 2015, may, 201505, 4707

    3000390, 15155, LO059, 2015, jun, 201506, 4728

    3000390, 15155, LO059, 2015, jul, 201507, 6572

    3000390, 15155, LO059, 2015, aug, 201508, 8053

    3000390, 15155, LO059, 2015, sep, 201509, 8084

    3000390, 15155, LO059, 2015, oct, 201510, 11670

    3000390, 15155, LO059, 2015, nov, 201511, 6674

    3000390, 15155, LO059, 2015, dec, 201512, 17694

    3000390, 15155, LO059, 2016, jan, 201601, 7827

    3000390, 15155, LO059, 2016, feb, 201602, 10909

    3000390, 15155, LO059, 2016, mar, 201603, 12717

    3000390, 15155, LO059, 2016, apr, 201604, 7248

    3000390, 15155, LO059, 2016, may, 201605, 8891

    3000390, 15155, LO059, 2016, jun, 201606, 7717

    3000390, 22731, LO059, 2016, jul, 201607, 5795

    3000390, 15155, LO059, 2016, jul, 201607, 7173

    3000390, 22731, LO059, 2016, aug, 201608, 10487

    3000390, 22731, LO059, 2016, sep, 201609, 9374

    3000390, 22731, LO059, 2016, oct, 201610, 16361

    3000390, 22731, LO059, 2016, nov, 201611, 10804

    3000390, 22731, LO059, 2016, dec, 201612, 24861

    3000390, 22731, LO059, 2017, jan, 201701, 12224

    3000390, 22731, LO059, 2017, feb, 201702, 11260

    3000390, 22731, LO059, 2017, mar, 201703, 9259

    3000390, 22731, LO059, 2017, apr, 201704, 8822

    3000390, 22731, LO059, 2017, may, 201705, 2350

    3000419, 11405, LO058, 2014, jan, 201401, 4758

    3000419, 11405, LO058, 2014, feb, 201402, 4737

    3000419, 11405, LO058, 2014, mar, 201403, 7825

    3000419, 11405, LO058, 2014, apr, 201404, 9883

    3000419, 11405, LO058, 2014, may, 201405, 9455

    3000419, 11405, LO058, 2014, jun, 201406, 11516

    3000419, 11405, LO058, 2014, jul, 201407, 9184

    3000419, 11405, LO058, 2014, aug, 201408, 7262

    3000419, 11405, LO058, 2014, sep, 201409, 9862

    3000419, 11405, LO058, 2014, oct, 201410, 17847

    3000419, 11405, LO058, 2014, nov, 201411, 10454

    3000419, 11405, LO058, 2014, dec, 201412, 17267

    3000419, 11405, LO058, 2015, jan, 201501, 6602

    3000419, 11405, LO058, 2015, feb, 201502, 6599

    3000419, 11405, LO058, 2015, mar, 201503, 1297

    3000419, 19423, LO058, 2015, mar, 201503, 10221

    3000419, 19423, LO058, 2015, apr, 201504, 12869

    3000419, 19423, LO058, 2015, may, 201505, 13998

    3000419, 19423, LO058, 2015, jun, 201506, 10873

    3000419, 19423, LO058, 2015, jul, 201507, 9549

    3000419, 19423, LO058, 2015, aug, 201508, 10009

    3000419, 19423, LO058, 2015, sep, 201509, 14736

    3000419, 19423, LO058, 2015, oct, 201510, 20378

    3000419, 19423, LO058, 2015, nov, 201511, 12520

    3000419, 19423, LO058, 2015, dec, 201512, 23264

    3000419, 19423, LO058, 2016, jan, 201601, 7419

    3000419, 19423, LO058, 2016, feb, 201602, 7485

    3000419, 19423, LO058, 2016, mar, 201603, 13120

    3000419, 19423, LO058, 2016, apr, 201604, 14334

    3000419, 19423, LO058, 2016, may, 201605, 15509

    3000419, 19423, LO058, 2016, jun, 201606, 14698

    3000419, 19423, LO058, 2016, jul, 201607, 14876

    3000419, 19423, LO058, 2016, aug, 201608, 12356

    3000419, 19423, LO058, 2016, sep, 201609, 13574

    3000419, 19423, LO058, 2016, oct, 201610, 23320

    3000419, 19423, LO058, 2016, nov, 201611, 16664

    3000419, 19423, LO058, 2016, dec, 201612, 29297

    3000419, 19423, LO058, 2017, jan, 201701, 12086

    3000419, 19423, LO058, 2017, feb, 201702, 10899

    3000419, 19423, LO058, 2017, mar, 201703, 12277

    3000419, 19423, LO058, 2017, apr, 201704, 14683

    3000419, 19423, LO058, 2017, may, 201705, 3494

];

NoConcatenate

Output:

load

CUSTOMER,

PERIOD,

Sum(AMOUNT) as Amount_CY

resident Data

group by CUSTOMER, PERIOD

;

left join(Final)

load

CUSTOMER,

PERIOD,

Sum(AMOUNT) as Amount_LY

group by CUSTOMER, PERIOD

;

load

CUSTOMER,

left(PERIOD,4)+1 & right(PERIOD,2) as PERIOD,

AMOUNT

resident Data

;

drop table Data;

282 Views
0 Likes
antoniotiman
Master III
Master III
‎2017-05-06 06:51 AM

Hi,

Temp:
LOAD CUSTOMER,  CONTRACT,  UNIT,  YEAR,  MONTH,
// PERIOD,
Date(MonthStart(Date#(PERIOD,'YYYYMM')),'YYYYMM') as PERIOD,
Date(MonthStart(Date#(PERIOD,'YYYYMM'),-12),'YYYYMM') as PERIOD_1,
AMOUNT
FROM
"https://community.qlik.com/message/1263119"
(html, codepage is 1252, embedded labels, table is @1);

LOAD CUSTOMER,PERIOD,AMOUNT as AMOUNT_CY,
Lookup('AMOUNT','PERIOD',PERIOD_1,'Temp') as AMOUNT_LY
Resident Temp;
DROP Table Temp;

Regards,

Antonio

282 Views