topic Re: Issue after concatenating in QlikView

Issue after concatenating

Anonymous — Thu, 05 Jan 2017 12:59:40 GMT

Dear All

I have two tables Table1- Sales and Table2- Outstanding. I have concatenated both tables.

Both have Date field of their own.

The Sales table field---> YearMonth is linked to calendar. I am given alias YearMonth to date field of Outstanding.

I am trying to create a report which has to show both Sales and Outstanding amounts for the previous month.

For which I use variable in Set analysis as vPrevmonth.

Variable is defined as below:

vPrevmonth = Date(addmonths(max([MonthYear]), -1), 'MMM-YYYY') .

However I do not get the desired result for Sales and Outstanding. The output works for only one field either Sales or Outstanding.

Not for both.

Is there any modification required? Kindly help.

Thanks & Regards

Chintan Gala

Re: Issue after concatenating

sunny_talwar — Thu, 05 Jan 2017 13:33:21 GMT

Would you be able to share a sample to show the issue?

Re: Issue after concatenating

petter — Thu, 05 Jan 2017 13:38:34 GMT

Without any sample data including a sample application it is only possible to give you general pointers to something that will help you:

Your problems seems to stem from having so-called role playing dimensions. You have a date that plays two roles one for sales and one for outstanding.

To solve this you should have a look at this information:

Canonical Date

and/or this:

Why You sometimes should Load a Master Table several times

Re: Issue after concatenating

Anonymous — Thu, 05 Jan 2017 13:46:40 GMT

Hello Sunny

I will try to attach a sample app after debugging with few records.

Till then I will summarize the issue in short:

Table 1 Sales:

Load A,B,C.....

Date(MonthYear,'MMM-YYYY') as MY

from. source......;

Calender:

Load A,B,C...

Date(MonthYeat,'MMM-YYYY') as MY

from source;..........

concatenate(Table1)

Table2: Outstanding

Load A,B,C

Date(outstandingdare,'MMM-YYYY') as MY

from source;

Then while making report values come in Sales but in OS it comes 0

Thanks & Regards

Chintan

Re: Issue after concatenating

sunny_talwar — Thu, 05 Jan 2017 22:00:56 GMT

That is kind of strange, but once you are able to share a sample, it might be easier to pin point the issue

Re: Issue after concatenating

Anonymous — Fri, 06 Jan 2017 10:41:47 GMT

Hello Sunny/Friends

Only the front end of Final app with single report having the expressions used and the script of app enough?

Thanks & Regards

Chintan Gala

Re: Issue after concatenating

sunny_talwar — Fri, 06 Jan 2017 10:45:58 GMT

To see what your issue is, those things should be enough. I don't know if we have to reload the app (hopefully not), then we might need the source data if possible

Re: Issue after concatenating

Anonymous — Fri, 06 Jan 2017 10:55:20 GMT

Correct.

Re: Issue after concatenating

Anonymous — Fri, 06 Jan 2017 12:23:42 GMT

Hello Sunny

I have attached the qvw.

Thanks & Regards

Chintan Gala

Re: Issue after concatenating

sunny_talwar — Fri, 06 Jan 2017 12:44:03 GMT

Try this in your script:

Date(MonthStart(MonthYear),'MMM-YYYY') as OS_MY,

and then reload the application and the problem should fix (I think)

Re: Issue after concatenating

Anonymous — Fri, 06 Jan 2017 12:51:16 GMT

Thanks Sunny, I will try and let u know!

Regards

Chintan

Re: Issue after concatenating

Anonymous — Fri, 06 Jan 2017 13:02:39 GMT

Hello Sunny

It still shows 0 for Sales. Also a small correction.

In Outstanding table the date is as date([Month Year],'MMM-YYYY') as OS_MY,

and not MonthYear. while reloading I understood.

Regards

Chintan

Re: Issue after concatenating

sunny_talwar — Fri, 06 Jan 2017 13:04:50 GMT

My bad, I didn't realize that there was another tab.... Updated it now, can you check

Re: Issue after concatenating

Anonymous — Fri, 06 Jan 2017 13:07:03 GMT

Hello Sunny

Also I have put

Date(MonthStart(MonthYear),'MMM-YYYY') as OS_MY,

for all dates in the Fact, Outstanding and Calender.

Note: The date in Fact and Outstanding are different which I am trying to alias with same name to use in variable. Is that causing issue? is it correct?

Thanks & Regards

Chintan

Re: Issue after concatenating

sunny_talwar — Fri, 06 Jan 2017 13:09:58 GMT

Can you reload the attached file and repost it after you have reloaded this?

Re: Issue after concatenating

Anonymous — Fri, 06 Jan 2017 13:10:19 GMT

while for the Outstanding date as

Date(MonthStart([Month Year] ),'MMM-YYYY') as OS_MY,

Re: Issue after concatenating

Anonymous — Fri, 06 Jan 2017 13:32:59 GMT

Dear Sunny

Kindly find attached.

Note: I made one change to date in Outstanding table as [Month Year] instead of [MonthYear]

Regards

Chintan

Re: Issue after concatenating

sunny_talwar — Fri, 06 Jan 2017 13:46:50 GMT

I think QlikView is not able to interpret your MonthYear field within the Fact table. You will need to use Date#() to help qlikview understand your MonthYear field and then you can use your own formatting. Lets say MonthYear looks like this

MM/YYYY

Date(MonthStart(Date#([MonthYear]), 'MM/YYYY')), 'MMM-YYYY') as OS_MY,

Now I don't have source data file, so don't really know how MonthYear looks like, but if you can reload the application once again with MonthYear field in your Fact table, I might be able to see how it looks like.

Also, read these

Why don’t my dates work?

QlikView Date fields‌

Get the Dates Right

Re: Issue after concatenating

Anonymous — Mon, 09 Jan 2017 05:43:06 GMT

Hello Sunny

I will check and let you know.

Thanks & Regards

Chintan Gala

Re: Issue after concatenating

Anonymous — Mon, 09 Jan 2017 12:48:49 GMT

Hello Sunny

Thanks a lot for your explanation.

Am still to check, just wanted one clarification. I am trying to figure out the format of dates in all tables from extractors.

If the field MonthYear comes in same format in all three tables -- 1) Fact ,2) Calendar and 3) Outstanding then that variable should work and show me Sales and Outstanding figures in the same report? (currently it shows Sales 0 as the date format in Fact table is in different format).

Is my above understanding from you correct?

Thanks & Regards

Chintan Gala