topic Re: If statement in QlikView

If statement

fanninam — Wed, 25 Nov 2020 16:16:04 GMT

Good Afternoon,

I'm wondering if anyone can help me with this. I need to include an if statement that says if Month equals 1 then Month equals 13 and Year equals -1.

The reason for this is that headcount is pulled on the first day of the month for the previous month headcount. For example, January 1st headcount number is the headcount number for December of the previous year. I already have Month-1 to take care of all the other months. The tricky part is always going to be January 1st.

Re: If statement

vishsaggi — Wed, 01 Feb 2017 23:26:35 GMT

Can you tell us your expected ouput and relevant fields you are using or share a sample app to quickly work on.?

Re: If statement

fanninam — Thu, 02 Feb 2017 14:09:07 GMT

This is the script. It is simple and pulling in monthly CSV files only. The only problem I have is the CSV file for January 1st every year is Month 1. Therefore, Month-1 makes it zero instead of making it Month 12..... of course the Year would need to be -1 as well. Any suggestions?

HPU:
LOAD [EMP FILE],
LASTNAME,
FIRSTNAME,
Company,
COMPANYCODE,
Division,
DEPT,
TITLE,
Status,
EjFTE,
[Business Unit Descrip],
TERMDATE,
STARTDATE,
SUPV,
text(Date(Date#(Month-1,'MM'),'MMM')) as MonthName,
Month,
Year,
ApplyMap('DeptGroups',[Business Unit Descrip], Null()) AS [Dept Groups],
ApplyMap('DeptGroups2',[Business Unit Descrip], Null()) AS Owner,
ApplyMap('DeptGroups3',[Business Unit Descrip], Null()) AS Area
FROM
[\\ftwqlikt001\Qlikview_Dev\External Data\Human Resources\HCUpload_*.csv]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Re: If statement

vishsaggi — Thu, 02 Feb 2017 14:27:50 GMT

What happens when you do this?

text(Date(Date#(Month-13,'MM'),'MMM')) as MonthName,

Re: If statement

fanninam — Thu, 02 Feb 2017 15:08:42 GMT

When I use

text(Date(Date#(Month-13,'MM'),'MMM')) as MonthName,

there is no change to Month but it causes MonthName not to populate with data.

Re: If statement

vishsaggi — Thu, 02 Feb 2017 15:22:30 GMT

Can you share a sample app and expected output as a screen shot ? Would help us to quickly figure out and work on?

You can scramble your data like here:

Preparing examples for Upload - Reduction and Data Scrambling

Re: If statement

fanninam — Thu, 02 Feb 2017 15:51:50 GMT

How do I share an app?

Re: If statement

vishsaggi — Thu, 02 Feb 2017 16:24:59 GMT

Click on reply and you can see Here:

Once you click on Advance editor you will have an attach icon in the bottom right.

Re: If statement

fanninam — Thu, 02 Feb 2017 16:46:34 GMT

I don't see "Use advanced editor"

Re: If statement

vishsaggi — Thu, 02 Feb 2017 17:03:57 GMT

Can you click on reply to your original query and still you don't see? Did you login to Qlik account?

Re: If statement

fanninam — Thu, 02 Feb 2017 17:26:19 GMT

Okay sorry. I'm still learning several things. I was able to attach the QVW. Hopefully this helps.

Re: If statement

juliedecoene — Thu, 02 Feb 2017 18:00:28 GMT

Hi Amber,

Can you try this?

Text(Date(AddMonths(MakeDate(Year, Month),-1),'MMM'))

Re: If statement

MarcoWedel — Thu, 02 Feb 2017 22:31:39 GMT

Hi,

another solution might be:

HPU:
LOAD
    DEPT,
    Status,
    EjFTE,
    MonthName(Date#(Month&'/'&Year,'M/YYYY'),-1) as MonthName,
    Month(Date#(Month,'M')) as  Month,
    Year
FROM HCUpload.csv (txt, utf8, embedded labels, delimiter is ',', msq);

hope this helps

regards

Marco