Load data based on condition

[nisha_rai]

Hi,

I have a scenario for showing the data in QV based on condition

Eg:

Year	Month	item	Sales
2017	1	A	12
2017	1	B	14
2017	2	A	15
2017	2	B	10
2017	3	A	17

Above is my table whichis store in Qvd form,

Now i have to check the Item with respect to Year Month

If in maximum month we have both item then we have to load the full table as it is, else we have to load the previous month data from table:

Result1: Find the max date from table and then find out the item

Maxdate=1/3/2017 Item =A(only)

in that base we have to load the data till previous month

Result2:

Maxdate=1/3/2017 Item =A & B

then we have to load the full table

In the above example , we are showing data till "Feb " only because in March we have only one month.

Please help me to resolve the above scenario.

Thanks & Regards,

Nisha

[PunamWagh · Accepted solution]

Dear Nisha,

Please have a look.

Regards,

Punam

[Kushal_Chawda]

Data:

LOAD Year,

     Month,

     floor(Year&num(Month,'00')) as MonthYear,

     item,

     Sales

FROM

[https://community.qlik.com/thread/268471]

(html, codepage is 1252, embedded labels, table is @1);

Left Join(Data)

load max(MonthYear) as MonthYear,

     1 as MaxFlag

Resident Data;

CountItem:

LOAD Count(DISTINCT item) as CountItem,

     MonthYear

Resident Data

where MaxFlag=1

Group by MonthYear;

LET vItemCount = Peek('CountItem',0,'CountItem');

LET vMaxMonth = Peek('MonthYear',0,'CountItem');

if $(vItemCount)=1 then

Final:

NoConcatenate

LOAD *

Resident Data

where MonthYear < '$(vMaxMonth)';

DROP Table Data;

ELSE

TRACE "Data is already loaded till date" ;

ENDIF

DROP Table CountItem;