topic Two files - two month formats... help! in QlikView

Two files - two month formats... help!

Sat, 01 Oct 2016 16:00:18 GMT

Hi guys,

I'm new to scripting so apologise for the simplicity of the request (I will get better!);

1. I have two files I'm loading into Qlik sense. Both have a month dimension/column which is needed in the app, they talk about different data relating to the same thing (a person, people), but applying the same months.

2. One file has the month format 1, 2, 3, 4 etc.... standing for January, February, March.

3. The other file has a Excel Formatting on, which is turning a full date into a month, e.g. 01/01/16 is the raw data, which displays as Jan-16, other rows show Feb-16, Mar-16 for instance.

4. When I upload the two, I'm fine with the 1,2,3, 4 representing months. But I get the string of numbers for the other months from the other file.

My question is A) how do I best/most efficiently resolve this without hard-coding months and in case of future files being added with the same month problem as the file which has Feb-16, Mar-16 and a string behind it.

B) where should I add this script? I see I have two options, one is to add it to 'Main'... where a heap of 'SET' criteria are pre-written. The other is to go to the detailed load script for each file.

I see in Main there is a SET criteria loading 'MonthNames....'

I see in the detailed data load (auto-generated) there is a heap of code related to each file and each column/dimension of data, there is a specific bit loading the dodgy Month format;

LOAD

[Month] AS [Bill Rate Raw Data.Month],

...... /loads of other script for other columns/dimensions...

FROM....

It's interesting it's changed the name of the Month column, because there is another file with a Month column... i guess to distinguish which data that month data is coming from.

Any help much appreciated. I've looked high and low for a decent explanation of how to do this, but they never seem to explain where to put the script!

Re: Two files - two month formats... help!

MarcoWedel — Mon, 03 Oct 2016 22:43:18 GMT

Hi,

I'd prefer loading both month sources as dual month values, i.e.

LOAD Month(Date#(monthfield,'M')) as monthfield
From OneFile;

LOAD Month(monthfield) as monthfield
From OtherFile;

If this field is already loaded as dual date from your Excel source, otherwise

Month(Date#(monthfield,'MM/DD/YY')) as monthfield

Month(Date#(monthfield,'MMM-YY')) as monthfield

might be necessary.

hope this helps

regards

Marco

Re: Two files - two month formats... help!

Wed, 05 Oct 2016 09:24:13 GMT

Thanks Marco - I will take a look and try this out.

Does this take into account that in one file, the monthfield facts/values are 1, 2, 3, 4, and in the second file the monthfield has facts/values which are Jan-16, Feb-16 - and these are formatted fields, so behind them lies the full date 01/01/2016 which is then displayed in Excel as Jan-16?

Re: Two files - two month formats... help!

MarcoWedel — Wed, 05 Oct 2016 21:35:55 GMT

Yes, I guess it should work.

Maybe you can upload small samples of both files to test with?

Thanks

regards

Marco