Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Arjunarao
Honored Contributor II

One year records

Hi All,

I want to show Sales values as zero for the values of min(Transaction_date) more than 365 days to today's date.

How to achieve this?

Trans_Date Trans_ID  Sales

10-June-2014  101  120

21-11-2014      101  130

21-11-2014      220 130

21-10-2014      220 340


In the above scenario, I want show the data as below:


Trans_Date Trans_ID  Sales

10-June-2014  101  0

21-11-2014      101  0

21-11-2014      220 130

21-10-2014      220 340




Please help me.

Thanks in advance.

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: One year records

Hi,

Check this

Temp:

load Flag,

    Trans_ID,

    Sales,

    date(date#(Transaction_Date,'MM/DD/YYYY'),'DD/MM/YYYY') as Transaction_Date,

num(Month(date(date#(Transaction_Date,'MM/DD/YYYY')))) as Month,

MonthName(date(date#(Transaction_Date,'MM/DD/YYYY'))) as MonthName,

'Q'&Ceil(num(Month(date(date#(Transaction_Date,'MM/DD/YYYY'))))/3) as Quarter,

    Year(date(date#(Transaction_Date,'MM/DD/YYYY'))) as Year inline [

Flag,Trans_ID,Transaction_Date,Sales

Y,1242,1/1/2014,272.76

Y,706,1/4/2015,306.36

Y,708,1/4/2015,700.44

Y,710,2/5/2014,631.7

Y,1242,5/5/2014,631.7

N,710,5/2/2015,947.52

Y,710,1/4/2015,340.56

Y,712,11/3/2014,1120.446

Y,712,11/5/2014,547.02

N,715,10/3/2014,543.328

Y,795,6/2/2015,87.62

Y,801,1/3/2014,376.605

Y,801,6/2/2015,301.284

Y,801,7/5/2015,234.43

Y,1112,9/1/2014,217.1

Y,1112,1/3/2014,189.72

Y,1126,2/5/2014,1283.84

N,1151,1/3/2014,133.11

Y,1242,2/5/2014,388.57

Y,1242,6/2/2015,230.496

Y,1242,1/4/2015,207.12

Y,1244,7/5/2015,208.794

N,1246,1/4/2015,245.61

Y,1248,2/5/2014,826.06

Y,1254,2/5/2014,372.648

Y,1112,9/2/2014,123

N,1112,9/28/2014,143

Y,1112,9/27/2014,217.1

N,1112,9/25/2014,256

Y,1112,9/30/2014,217.1

Y,1112,9/29/2014,60

N,710,5/2/2014,947.52

Y,795,6/2/2014,87.62

Y,801,6/2/2014,301.284

Y,801,7/5/2014,234.43

Y,1242,6/2/2014,230.496

Y,1244,7/5/2014,208.794

Y,1242,9/29/2015,280.496

Y,1242,9/29/2014,320

Y,9999,4/25/2014,456

Y,9999,9/27/2014,320

Y,9999,9/29/2014,278.794

Y,9999,9/30/2015,124

Y,9999,9/30/2014,321

Y,1242,7/30/2015,124

Y,1242,8/30/2014,321

Y,9999,10/01/2015,423

Y,9999,8/30/2015,654

Y,9999,2/28/2015,344

Y,9999,10/30/2014,321

Y,9999,11/30/2014,423

Y,9999,12/30/2014,654

];

LEFT JOIN(Temp)

LOAD

Trans_ID,

Date(Min(Transaction_Date)) AS MinDate

RESIDENT Temp

GROUP BY Trans_ID;

Data:

LOAD

*,

If(Transaction_Date <= Date(MinDate + 365), Sales, 0) AS Sales_CALC

RESIDENT Temp;

DROP TABLE Temp;

20 Replies

Re: One year records

try this

Data:

load Flag,

    Trans_ID,

    Sales,

    date(date#(Transaction_Date,'MM/DD/YYYY'),'DD/MM/YYYY') as Transaction_Date,

num(Month(date(date#(Transaction_Date,'MM/DD/YYYY')))) as Month,

MonthName(date(date#(Transaction_Date,'MM/DD/YYYY'))) as MonthName,

'Q'&Ceil(num(Month(date(date#(Transaction_Date,'MM/DD/YYYY'))))/3) as Quarter,

    Year(date(date#(Transaction_Date,'MM/DD/YYYY'))) as Year inline [

Flag,Trans_ID,Transaction_Date,Sales

Y,1242,1/1/2014,272.76

Y,706,1/4/2015,306.36

Y,708,1/4/2015,700.44

Y,710,2/5/2014,631.7

Y,1242,5/5/2014,631.7

N,710,5/2/2015,947.52

Y,710,1/4/2015,340.56

Y,712,1/3/2014,1120.446

Y,712,2/5/2014,547.02

N,715,1/3/2014,543.328

Y,795,6/2/2015,87.62

Y,801,1/3/2014,376.605

Y,801,6/2/2015,301.284

Y,801,7/5/2015,234.43

Y,1112,9/1/2014,217.1

Y,1112,1/3/2014,189.72

Y,1126,2/5/2014,1283.84

N,1151,1/3/2014,133.11

Y,1242,2/5/2014,388.57

Y,1242,6/2/2015,230.496

Y,1242,1/4/2015,207.12

Y,1244,7/5/2015,208.794

N,1246,1/4/2015,245.61

Y,1248,2/5/2014,826.06

Y,1254,2/5/2014,372.648

Y,1112,9/2/2014,123

N,1112,9/28/2014,143

Y,1112,9/27/2014,217.1

N,1112,9/25/2014,256

Y,1112,9/30/2014,217.1

Y,1112,9/29/2014,60

N,710,5/2/2014,947.52

Y,795,6/2/2014,87.62

Y,801,6/2/2014,301.284

Y,801,7/5/2014,234.43

Y,1242,6/2/2014,230.496

Y,1244,7/5/2014,208.794

Y,1242,9/29/2015,280.496

Y,1242,9/29/2014,320

Y,1242,9/27/2015,456

Y,1242,9/27/2014,320

Y,1244,9/29/2014,278.794

Y,1242,9/30/2015,124

Y,1242,9/30/2014,321

Y,1242,7/30/2015,124

Y,1242,8/30/2014,321

Y,1242,5/30/2015,423

Y,1242,8/30/2015,654

Y,1242,2/28/2015,344

Y,1242,10/30/2014,321

Y,1242,11/30/2014,423

Y,1242,12/30/2014,654

];

left join (Data)

Load date(min(Transaction_Date),'DD/MM/YYYY') as Transaction_Date,

Trans_ID,

'1' as MinDateFlag

Resident Data

where num(Transaction_Date)>num(today()-365)

Group by Trans_ID;

Final:

NoConcatenate

LOAD *,

if(MinDateFlag=1,0,Sales) as SalesFinal

Resident Data;

DROP Table Data;

MVP & Luminary
MVP & Luminary

Re: One year records

Hi,

Try this script

Data:

LOAD

*,

If(Transaction_Date >= date(today()-365,'DD/MM/YYYY'), Sales, 0) AS Sales_Calc;

load Flag,

     Trans_ID,

     Sales,

     date(date#(Transaction_Date,'MM/DD/YYYY'),'DD/MM/YYYY') as Transaction_Date,

num(Month(date(date#(Transaction_Date,'MM/DD/YYYY')))) as Month,

MonthName(date(date#(Transaction_Date,'MM/DD/YYYY'))) as MonthName,

'Q'&Ceil(num(Month(date(date#(Transaction_Date,'MM/DD/YYYY'))))/3) as Quarter,

     Year(date(date#(Transaction_Date,'MM/DD/YYYY'))) as Year inline [

Flag,Trans_ID,Transaction_Date,Sales

Y,1242,1/1/2014,272.76

Y,706,1/4/2015,306.36

Y,708,1/4/2015,700.44

Y,710,2/5/2014,631.7

Y,1242,5/5/2014,631.7

N,710,5/2/2015,947.52

Y,710,1/4/2015,340.56

Y,712,1/3/2014,1120.446

Y,712,2/5/2014,547.02

N,715,1/3/2014,543.328

Y,795,6/2/2015,87.62

Y,801,1/3/2014,376.605

Y,801,6/2/2015,301.284

Y,801,7/5/2015,234.43

Y,1112,9/1/2014,217.1

Y,1112,1/3/2014,189.72

Y,1126,2/5/2014,1283.84

N,1151,1/3/2014,133.11

Y,1242,2/5/2014,388.57

Y,1242,6/2/2015,230.496

Y,1242,1/4/2015,207.12

Y,1244,7/5/2015,208.794

N,1246,1/4/2015,245.61

Y,1248,2/5/2014,826.06

Y,1254,2/5/2014,372.648

Y,1112,9/2/2014,123

N,1112,9/28/2014,143

Y,1112,9/27/2014,217.1

N,1112,9/25/2014,256

Y,1112,9/30/2014,217.1

Y,1112,9/29/2014,60

N,710,5/2/2014,947.52

Y,795,6/2/2014,87.62

Y,801,6/2/2014,301.284

Y,801,7/5/2014,234.43

Y,1242,6/2/2014,230.496

Y,1244,7/5/2014,208.794

Y,1242,9/29/2015,280.496

Y,1242,9/29/2014,320

Y,1242,9/27/2015,456

Y,1242,9/27/2014,320

Y,1244,9/29/2014,278.794

Y,1242,9/30/2015,124

Y,1242,9/30/2014,321

Y,1242,7/30/2015,124

Y,1242,8/30/2014,321

Y,1242,5/30/2015,423

Y,1242,8/30/2015,654

Y,1242,2/28/2015,344

Y,1242,10/30/2014,321

Y,1242,11/30/2014,423

Y,1242,12/30/2014,654

];

Regards,

Jagan.

Re: One year records

Data:

load Flag,

    Trans_ID,

    Sales,

    date(date#(Transaction_Date,'MM/DD/YYYY'),'DD/MM/YYYY') as Transaction_Date,

num(Month(date(date#(Transaction_Date,'MM/DD/YYYY')))) as Month,

MonthName(date(date#(Transaction_Date,'MM/DD/YYYY'))) as MonthName,

'Q'&Ceil(num(Month(date(date#(Transaction_Date,'MM/DD/YYYY'))))/3) as Quarter,

    Year(date(date#(Transaction_Date,'MM/DD/YYYY'))) as Year inline [

Flag,Trans_ID,Transaction_Date,Sales

Y,1242,1/1/2014,272.76

Y,706,1/4/2015,306.36

Y,708,1/4/2015,700.44

Y,710,2/5/2014,631.7

Y,1242,5/5/2014,631.7

N,710,5/2/2015,947.52

Y,710,1/4/2015,340.56

Y,712,1/3/2014,1120.446

Y,712,2/5/2014,547.02

N,715,1/3/2014,543.328

Y,795,6/2/2015,87.62

Y,801,1/3/2014,376.605

Y,801,6/2/2015,301.284

Y,801,7/5/2015,234.43

Y,1112,9/1/2014,217.1

Y,1112,1/3/2014,189.72

Y,1126,2/5/2014,1283.84

N,1151,1/3/2014,133.11

Y,1242,2/5/2014,388.57

Y,1242,6/2/2015,230.496

Y,1242,1/4/2015,207.12

Y,1244,7/5/2015,208.794

N,1246,1/4/2015,245.61

Y,1248,2/5/2014,826.06

Y,1254,2/5/2014,372.648

Y,1112,9/2/2014,123

N,1112,9/28/2014,143

Y,1112,9/27/2014,217.1

N,1112,9/25/2014,256

Y,1112,9/30/2014,217.1

Y,1112,9/29/2014,60

N,710,5/2/2014,947.52

Y,795,6/2/2014,87.62

Y,801,6/2/2014,301.284

Y,801,7/5/2014,234.43

Y,1242,6/2/2014,230.496

Y,1244,7/5/2014,208.794

Y,1242,9/29/2015,280.496

Y,1242,9/29/2014,320

Y,1242,9/27/2015,456

Y,1242,9/27/2014,320

Y,1244,9/29/2014,278.794

Y,1242,9/30/2015,124

Y,1242,9/30/2014,321

Y,1242,7/30/2015,124

Y,1242,8/30/2014,321

Y,1242,5/30/2015,423

Y,1242,8/30/2015,654

Y,1242,2/28/2015,344

Y,1242,10/30/2014,321

Y,1242,11/30/2014,423

Y,1242,12/30/2014,654

];

left join (Data)

Load date(min(Transaction_Date),'DD/MM/YYYY') as Transaction_Date,

Trans_ID,

'1' as MinDateFlag

Resident Data

Group by Trans_ID;

Final:

NoConcatenate

LOAD *,

if(MinDateFlag=1 and num(Transaction_Date)>num(Today()-365),0,Sales) as SalesFinal

Resident Data;

DROP Table Data;

Partner
Partner

Re: One year records

I think this should work..

Table:

load

Trans_Date,

if((num(today())-num(Trans_Date))>364,0,Trans_ID  Sales) as Trans_ID  Sales

from Table;

Arjunarao
Honored Contributor II

Re: One year records

Hi Jagan,condition is when min(Transaction_Date)>=365 then Sales value=0.

Will your logic works?

Arjunarao
Honored Contributor II

Re: One year records

Hi All,

Both solutions are not working.

min(Trans_Date)>365 then Sales is zero for those Trans_IDs  for all the TransactionDates.

Please help me.

MVP & Luminary
MVP & Luminary

Re: One year records

Hi Arjun,

Try like this

Data_Temp:

load Flag,

     Trans_ID,

     Sales,

     date(date#(Transaction_Date,'MM/DD/YYYY'),'DD/MM/YYYY') as Transaction_Date,

num(Month(date(date#(Transaction_Date,'MM/DD/YYYY')))) as Month,

MonthName(date(date#(Transaction_Date,'MM/DD/YYYY'))) as MonthName,

'Q'&Ceil(num(Month(date(date#(Transaction_Date,'MM/DD/YYYY'))))/3) as Quarter,

     Year(date(date#(Transaction_Date,'MM/DD/YYYY'))) as Year inline [

Flag,Trans_ID,Transaction_Date,Sales

Y,1242,1/1/2014,272.76

Y,706,1/4/2015,306.36

Y,708,1/4/2015,700.44

Y,710,2/5/2014,631.7

Y,1242,5/5/2014,631.7

N,710,5/2/2015,947.52

Y,710,1/4/2015,340.56

Y,712,1/3/2014,1120.446

Y,712,2/5/2014,547.02

N,715,1/3/2014,543.328

Y,795,6/2/2015,87.62

Y,801,1/3/2014,376.605

Y,801,6/2/2015,301.284

Y,801,7/5/2015,234.43

Y,1112,9/1/2014,217.1

Y,1112,1/3/2014,189.72

Y,1126,2/5/2014,1283.84

N,1151,1/3/2014,133.11

Y,1242,2/5/2014,388.57

Y,1242,6/2/2015,230.496

Y,1242,1/4/2015,207.12

Y,1244,7/5/2015,208.794

N,1246,1/4/2015,245.61

Y,1248,2/5/2014,826.06

Y,1254,2/5/2014,372.648

Y,1112,9/2/2014,123

N,1112,9/28/2014,143

Y,1112,9/27/2014,217.1

N,1112,9/25/2014,256

Y,1112,9/30/2014,217.1

Y,1112,9/29/2014,60

N,710,5/2/2014,947.52

Y,795,6/2/2014,87.62

Y,801,6/2/2014,301.284

Y,801,7/5/2014,234.43

Y,1242,6/2/2014,230.496

Y,1244,7/5/2014,208.794

Y,1242,9/29/2015,280.496

Y,1242,9/29/2014,320

Y,1242,9/27/2015,456

Y,1242,9/27/2014,320

Y,1244,9/29/2014,278.794

Y,1242,9/30/2015,124

Y,1242,9/30/2014,321

Y,1242,7/30/2015,124

Y,1242,8/30/2014,321

Y,1242,5/30/2015,423

Y,1242,8/30/2015,654

Y,1242,2/28/2015,344

Y,1242,10/30/2014,321

Y,1242,11/30/2014,423

Y,1242,12/30/2014,654

];

MinDateTemp:

LOAD

Min(Transaction_Date) AS MinDate

RESIDENT Data_Temp;

LET vMinDate = Num(Peek('MinDate'));

DROP TABLE MinDateTemp;

Data:

LOAD

*,

If(Transaction_Date > Date('$(vMinDate)' + 365), Sales, 0)  AS Sales_Calc

RESIDENT Data_Temp;

DROP TABLE Data_Temp;

Hope this helps you.

Regards,

Jagan.

MVP & Luminary
MVP & Luminary

Re: One year records

Used one table to pull min date and stored it in variable and used that variable in the load statement again.  Based on your requirement you can change the logic.

Regards,

Jagan.

Partner
Partner

Re: One year records

See the attached qvw (Second tab).