topic Re: Data Preparation in Load in QlikView

Data Preparation in Load

Wed, 25 Nov 2020 16:16:04 GMT

Hi All, I have a complicated data set and I am having a hard time creating a straight table to show YTD vs Prior Year for the same months*. If anybody can assist please provide your input. If you can provide a workbook that would be much appreciate it.

This is the current stay of the data:

Date	Health	401K	Training
1/1/2014	15	10	3
1/2/2015	123	15	4
2/6/2015	15	13	5
3/1/2015	20	51	5
4/1/2015	63	32	32
5/1/2015	15	26	12
6/1/2015	26	46	15
1/6/2016	32	32	31
2/9/2016	15	14	12

This is the straight table I want to create:

Fund Name	YTD as Feb 2016	YTD as Feb 2015	Variance
Health	47	138	-91
401K	46	64	-18
Training	43	9	34

*Note that there might be different dates in the same month. I want to compare the months irrespective of the dates.

Re: Data Preparation in Load

Anonymous — Thu, 23 Jun 2016 04:17:54 GMT

Hi ,

Can you say which aggregation function you used to calculate in your straight table output.

Re: Data Preparation in Load

sunny_talwar — Thu, 23 Jun 2016 04:34:58 GMT

You want something like this?

I used a crosstable to transpose your data, not sure if you already have it like this or if you have different columns for each FundName.

See if the attached is helpful.

Script:

Table:

CrossTable(FundName, Value, 3)

LOAD Date,

MonthName(Date) as MonthYear,

Month(Date) as Month,

Health,

[401K],

Training

INLINE [

Date, Health, 401K, Training

1/1/2014, 15, 10, 3

1/2/2015, 123, 15, 4

2/6/2015, 15, 13, 5

3/1/2015, 20, 51, 5

4/1/2015, 63, 32, 32

5/1/2015, 15, 26, 12

6/1/2015, 26, 46, 15

1/6/2016, 32, 32, 31

2/9/2016, 15, 14, 12

];

Dimension:

FundName

Expressions

1) =Sum({<Date = {"$(='>=' & Date(YearStart(Max(Date))) & '<=' & Date(MonthEnd(Max(Date))))"}, MonthYear, Month>}Value)

2) =Sum({<Date = {"$(='>=' & Date(YearStart(Max(Date), -1)) & '<=' & Date(MonthEnd(Max(Date), -12)))"}, MonthYear, Month>}Value)

3) =Column(1) - Column(2)

Best,

Sunny

Re: Data Preparation in Load

mightyqlikers — Thu, 23 Jun 2016 04:41:02 GMT

hi,

Find the attached.

$@M.

Re: Data Preparation in Load

Anonymous — Thu, 23 Jun 2016 09:25:06 GMT

Hi ,

Please find this Attachment . Maybe it match your requirement .

Re: Data Preparation in Load

Fri, 24 Jun 2016 14:42:24 GMT

Thanks. You guys rock!

Re: Data Preparation in Load

oknotsen — Fri, 24 Jun 2016 14:46:28 GMT

If your question is now answered, please flag the Correct Answer and possible Helpful answers.

If not, please make clear with which part of this topic you still like to get help .