Qlik Community

Ask a Question

QlikView Administration

Discussion Board for collaboration on QlikView Management.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
cancel
Showing results for 
Search instead for 
Did you mean: 
upaliwije
Creator II
Creator II

Script

Dear Friends

I have following table

 

POLICY_NODATEPREMIUM
00KU1C0007484376628005
01KU1C0008284376016780
03KU1D0022634376617379
04K41C0000064375519085
04KU1D0031664376132009
KU00051D0039554376560719.94
KU00061B0029344373923364
KU00061B0029714375725923.07
KU00061D00463043705-24807.5
KU00071C0036894374512365.25
KU00071C0037614376718034
KU00071F00296943697-5266.63
KU00101A00335243734-18155

 

In my load script I have created a variable

LET vMonth=Date(Makedate(2019,10),'YYYYMM');

and then

Load *

from table

where DATE(DATE,'YYYYMM')=$(vMonth);

 But  it loads no records at all . Can you please correct my script pls so that records matching  my where condition can be loaded. 

1 Solution

Accepted Solutions
Frank_Hartmann
Master II
Master II

or maybe just use textfunction:

 

let vMonth= Date(Makedate(2019,10),'YYYYMM');

LOAD POLICY_NO, 
     DATE, 
     PREMIUM
FROM
[...]
where text(DATE(DATE,'YYYYMM'))=$(vMonth);

 

 

View solution in original post

6 Replies
Vegar
Partner
Partner

By typing date(DATE, 'YYYYMM') you still have the underlying original date integer.

Try this instead 

LET vMonth=Date(Makedate(2019,10),'YYYYMM');

and then

Load *

from table

where monthstart(DATE)=$(vMonth);

 

Or try this

LET vMonth=FLOOR(Makedate(2019,10));

and then

Load *

from table

where monthstart(DATE)=$(vMonth);

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
asinha1991
Creator III
Creator III

maybe this?

DATE(DATE,'YYYYMM')='$(vMonth)'

JGMDataAnalysis
Creator II
Creator II

LET vMonth = Num(MakeDate(2019, 10));

NOCONCATENATE LOAD * FROM Table

WHERE Num(MonthStart(DATE)) = $(vMonth);

upaliwije
Creator II
Creator II

Thanks

 

But it returns zero records. please share a sample qvw

Frank_Hartmann
Master II
Master II

or maybe just use textfunction:

 

let vMonth= Date(Makedate(2019,10),'YYYYMM');

LOAD POLICY_NO, 
     DATE, 
     PREMIUM
FROM
[...]
where text(DATE(DATE,'YYYYMM'))=$(vMonth);

 

 

View solution in original post

JGMDataAnalysis
Creator II
Creator II

I don't have QlikView installed on my personal notebook.

However, I copied the script used and attached .qvf file.

LET vMonth = Num(MakeDate(2019, 10));

Test:
NOCONCATENATE 
LOAD *
WHERE Num(MonthStart(DATE)) = $(vMonth)
;
LOAD * INLINE [
  POLICY_NO,	  DATE,	 PREMIUM
  00KU1C000748,	  43766, 28005
  01KU1C000828,	  43760, 16780
  03KU1D002263,	  43766, 17379
  04K41C000006,	  43755, 19085
  04KU1D003166,	  43761, 32009
  KU00051D003955, 43765, 60719.94
  KU00061B002934, 43739, 23364
  KU00061B002971, 43757, 25923.07
  KU00061D004630, 43705, -24807.5
  KU00071C003689, 43745, 12365.25
  KU00071C003761, 43767, 18034
  KU00071F002969, 43697, -5266.63
  KU00101A003352, 43734, -18155
];

Result:

clipboard_image_0.png