topic Re: Date problem in Set Analysis Expression in QlikView

Date problem in Set Analysis Expression

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

Hi All,

I am attaching both Sample QVW as well as sample excel file for my query which are in two parts.

1. My sample excel file is having different sheets with sheet name as Dec 16, Jan 17, Feb 17 and so on. But all the sheets have similar format data. I need a way to dynamically load all the sheets through one load statement. I think that it is possible but I am not aware of the process. My load statement for Dec 16 is as following :

LOAD [Mon-FY], Month as Mon, LEFT(FY,4) as Period, [GL Code], Description, [Field 1], [Field 2], [Field 3], [Field 4],

[Field 5]

FROM [Sample Excel File.xlsx]

(ooxml, embedded labels, table is [Dec 16], filters(Remove(Row, Pos(Top, 1)),Remove(Col, Pos(Top, 19)),Remove(Col, Pos(Top, 19))));

I need to load all other sheets data also through this load statement. Kindly suggest me if possible but without using ODBC.

2. I am trying to make a straight table chart but some error are there in my set analysis expression. I have made one set analysis expression for example purpose. And also I have made a text object to check whether I am doing right or wrong. There is error in my set analysis expression which I am not able to figure it out. My month selected is Jun but I want to make chart for May using variable vFirstPeriod which I have defined in an input box. Kindly Suggest me what wrong I am doing in my set analysis expression.

Regards,

Manish Prasad

Re: Date problem in Set Analysis Expression

techvarun — Wed, 26 Jul 2017 04:54:24 GMT

For Question number 1 Refer the below thread for the answer.

need to load all the sheets in excel at time ?

Re: Date problem in Set Analysis Expression

arvind_patil — Wed, 26 Jul 2017 05:04:45 GMT

HI Manish,

It's Possible Please refer below code And follow instructions which are in a comment:

Let vFileName ='C:\Users\USER-12\Desktop\Multi Sheet Excel.xls';

Temp_Sheets:

LOAD Sheet as S // Number of Sheets:like I Have 3 sheets 1 2 3 so i Give 3 Value in field Sheet

FROM

(biff, embedded labels, table is [0$]); // 0 is my defalut sheet where i mention my Total number sheet

let vMaxSheet = peek('S', -1, 'Temp_Sheets');// Count of Sheets

DROP TABLE Temp_Sheets;

for vSheetNo = 1 to $(vMaxSheet)

Data:

LOAD

FROM $(vFileName)

(biff, embedded labels, table is [$(vSheetNo)$]);// Sheet Number must be in sequence like 0 1 2

next

Thanks,

Arvind Patil

Re: Date problem in Set Analysis Expression

PrashantSangle — Wed, 26 Jul 2017 05:07:50 GMT

Hi,

modify your expression to below

1: to convert your date field into date from numeric value =Date([Mon-FY],'MMM-YY')

2: In your chart you are using sum(Kolkata)

there is no such field named Kolkata in your script. Please check.

Read below blog to learn about date

https://community.qlik.com/docs/DOC-1208

https://community.qlik.com/blogs/qlikviewdesignblog/2013/02/19/why-don-t-my-dates-work

https://community.qlik.com/blogs/qlikviewdesignblog/2012/06/07/get-the-dates-right

Re: Date problem in Set Analysis Expression

manpri7078 — Wed, 26 Jul 2017 05:18:18 GMT

Hi Prashant,

I have by mistake uploaded another qlikview document. Now I have uploaded right one.

Now please suggest what I am doing wrong in my set analysis expression.

Regards,

Manish Prasad

Re: Date problem in Set Analysis Expression

PrashantSangle — Wed, 26 Jul 2017 05:43:25 GMT

Hi,

I have checked your variable definition. In that you are inserting string so convert string to date you have to use date#()

not date()

update your expression like

SUM({<[Mon-FY]={"$(=num(Date(vFirstPeriod,'MMM-YY')))"}>}[Field 1])

and your [Mon-FY] field in num format so either floor it your script or format your set analysis as per format

SUM({<[Mon-FY]={"$(=num(Date(vFirstPeriod,'MMM-YY'),,'####.000000'))"}>}[Field 1])

Regards,

Prashant

Re: Date problem in Set Analysis Expression

manpri7078 — Wed, 26 Jul 2017 05:57:51 GMT

Hi Prashant,

I have modified my set analysis expression as per your advice, but still no data is coming.

Please see my first chart in my modified document which is attached herewith.

Regards

Manish Prasad

Re: Date problem in Set Analysis Expression

PrashantSangle — Wed, 26 Jul 2017 06:03:35 GMT

Hi,

You are still using date() replace it with date#().

There 2 semicolon in your expression remove it.

modify your expression as

SUM({<[Mon-FY]={"$(=num(Date#(vFirstPeriod,'MMM-YY'),'####.000000'))"}>}[Field 1])

After apply above changes still you are not getting value because there is no date available for May 17.

Regards,

Prashant

Re: Date problem in Set Analysis Expression

manpri7078 — Wed, 26 Jul 2017 06:17:28 GMT

Hi Prashant,

Still no data is coming after modifying my expression as

SUM({<[Mon-FY]={"$(=num(Date#(vFirstPeriod,'MMM-YY'),'####.000000'))"}>}[Field 1]).

Although Data for May-17 is available as also from Dec-16 to Jun-17.

Please check it on my attached document and suggest me what is wrong there.

Regards,

Manish Prasad

Re: Date problem in Set Analysis Expression

PrashantSangle — Wed, 26 Jul 2017 06:27:32 GMT

Hi,

Your input value is May-17 when you convert it into date it will take it as 01-05-17 and data which you have in month may on date 05-05-17 both are not going to match.

In your script convert add below modification

monthname([Mon-FY]) as monthName

and use MonthName field in your set expression.

update your set expression like

SUM({<monthName={"$(=monthName(Date#(vFirstPeriod,'MMM-YY')))"}>}[Field 1]).

Regards,

Re: Date problem in Set Analysis Expression

manpri7078 — Wed, 26 Jul 2017 07:06:56 GMT

Hi Prashant,

I have modified my qlikview document as per your suggestions but still there is problem. Can you attach my document after modifications as suggested by you so that I can re-check your app with that of mine as to what steps I am missing.

Regards,

Manish Prasad

Re: Date problem in Set Analysis Expression

PrashantSangle — Wed, 26 Jul 2017 07:25:33 GMT

Hi,

Please find attachment.

I did changes in script, chart and your Month List box

In Month List box use expression as Aggr(Mon,Mon)

Regards,

Prashant

Re: Date problem in Set Analysis Expression

manpri7078 — Wed, 26 Jul 2017 07:47:20 GMT

Hi Prashant,

Thanks for your guide and suggestions. Now I have been able to accomplish my task.

Regards,

Manish Prasad