Script

[upaliwije]

Dear Friends

I have following table

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

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. 

[Vegar]

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);

[asinha1991]

maybe this?

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

[JGMDataAnalysis]

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

NOCONCATENATE LOAD * FROM Table

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

[upaliwije]

Thanks

But it returns zero records. please share a sample qvw

[Frank_Hartmann · Accepted solution]

or maybe just use textfunction:

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

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

[JGMDataAnalysis]

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: