topic Re: Dynamic Chart and Dynamic Files in QlikView

Dynamic Chart and Dynamic Files

Tue, 15 Oct 2013 16:25:42 GMT

Full Text Reason Code	Days Bucket	Amount
101 - Cash Application Issue	16-30 Days	10
101 - Cash Application Issue	16-30 Days	20
101 - Cash Application Issue	16-30 Days	30
101 - Cash Application Issue	16-30 Days	40
101 - Cash Application Issue	1-15 Days	50
101 - Cash Application Issue	1-15 Days	15
101 - Cash Application Issue	1-15 Days	25
100 - Please Select	16-30 Days	35
100 - Please Select	1-15 Days	2
100 - Please Select	16-30 Days	3
100 - Please Select	16-30 Days	4

I'm displaying a report Pivot table like this:

	(Count Days Bucket)		(Sum of Amount)
Row Labels	1-15 Days	16-30 Days	1-15 Days	16-30 Days
100 - Please Select	1	3	2	42
101 - Cash Application Issue	3	4	90	100

Now the next challenge is that this only data for 1 month.

So each month has its own excel file.

I would like the report to search all excel files in the directory (they have a naming convention: YEAR_MONTH.xlsx) and show me a Bar Chart with 2 dimensions: YEAR_MONTH on x axis, and Sum(Amount) (based on Raw Data would be 234 for this example) on y axis.

I would like also every time I copy a new file with a new month to the report directory, the chart auto updates with a new month for infinite months.

Last but not the least I would like to click on a bar corresponding to a month a automatically show the corresponding Pivot Report table for that month.

This is followed by this previous thread: http://community.qlik.com/thread/95965, but you don't need to read the previous one because it was a confusion already.

Thanks,

Miguel

Re: Dynamic Chart and Dynamic Files

vivientexier — Tue, 15 Oct 2013 16:59:39 GMT

If in your directory there is only this type of file : YEAR_MONTH.xlsx, then, you can load all the file of your directory by using " *.xlsx " instead of " 2013_10.xlsx " or you can try "????_??.xlsx".

"*" replace all caracters and no limit of caracters

"?" replace only one caracter.

Re: Dynamic Chart and Dynamic Files

Carlos_Reyes — Tue, 15 Oct 2013 17:35:58 GMT

AS Vivien said, if you only have that type of file in your directory you can use the wildcards proposed.

As for the automatic pivot table when clicking on a bar, that's not a default behavior so the only way to achieve that without getting into macros, because I don't use them and don't know how to code them, would be to enable that type of chart in the Fast Type Change option. So.. once the user has selected a bar, it'll have to click on that icon to see the pivot table representation.

Perhaps you could simulate that kind of behavior using the show conditional option in the ( Object Properties: Layout tab ), So, you'll have two objects, the bar chart and the pivot table.

The bar chart would have the next expression:

GetPossibleCount(YEAR_MONTH) > 1

While the pivot table the opposite:

GetPossibleCount(YEAR_MONTH) = 1

Whenever the user selects only one YEAR_MONTH, the pivot table will be shown, otherwise the bar chart will be visible.

Hope it helps!

Re: Dynamic Chart and Dynamic Files

Wed, 16 Oct 2013 10:45:18 GMT

So here's my strategy to implement this.

I'll post here some pseudo code and you tell me if it's a good idea.

Re: Dynamic Chart and Dynamic Files

carlos.reyes.qv

do not forget that I just want to create a pivot table in my qlikview report and I don't want to create a new qlikview report table each time a new month is added. So I didn't understand your help, but thanks anyway.

Begin Pseudocode:

var fileArray = GetFilesFromDir();

var finalTable;

foreach(file in fileArray)

{

var yearMonth = GetFromFile(file);

var currentTable = LOAD yearMonth as YEAR_MONTH, * FROM file;

Concatenate(finalTable, currentTable);

}

For the bar chart:

Use column YEAR_MONTH as 1st dimension. So when I click on a month it will filter the pivot table automatically.

End Pseudocode.

Thanks,

Miguel

Re: Re: Dynamic Chart and Dynamic Files

Carlos_Reyes — Wed, 16 Oct 2013 15:25:50 GMT

Check the attachment.

It contains a small example of what I tried to explain in my previous post.

Hope it helps!