topic Json like data to table? in QlikView

Json like data to table?

Wed, 18 Jul 2012 09:07:56 GMT

Hi Everybody,

I need some help. One of the fields I am supposed to refer to is returning, so called Json like data - please see below. I am looking for a way to create columns with corresponding labels and consisting values. I would like to do this with scripting so that I can connect/display fields to rest of the report.

My data comes from a flat table, not from web etc and each row might have different contents and I cannot use Inline. (please see attached)

Any advice or help will be appreciated.

Best,

What I have in a single cell is:

{"F.13-17":722,"F.18-24":544,"M.18-24":469,"M.13-17":414,"F.25-34":126,"M.25-34":116,"M.35-44":91,"F.35-44":86,"F.45-54":41,"M.45-54":35,"M.55+":19,"F.55+":18,"U.18-24":8,"U.35-44":5,"U.UNKNOWN":4,"U.45-54":3,"U.25-34":2}

What I want is a table like:

F.13-17	F.18-24	M.18-24	M.13-17	F.25-34	M.25-34	M.35-44	F.35-44	F.45-54	M.45-54	M.55+	F.55+	U.18-24	U.35-44	U.UNKNOWN	U.45-54	U.25-34
722	544	469	414	126	116	91	86	41	35	19	18	8	5	4	3	2

Re: Json like data to table?

Wed, 18 Jul 2012 10:31:27 GMT

kind a veird idea but i think it could help you...

Re: Json like data to table?

Thu, 19 Jul 2012 01:14:25 GMT

Hi Wojciech,

Thanks for your help. It is very helpful. But when I pivot the input data in excel; realized some discrepancy with new QVW output and excel. For example, F.13-17 is summing up correctly but U.UNKNOWN is left as 16 where is shall be 26. I assume this happens because the the values are not in proper order within the given cell or sometimes missing.

But I guess crosstable should be the way to go - although I am not that confident with crosstables yet

Row Labels	Sum of Count
F.13-17	10919
F.18-24	8528
F.25-34	2157
F.35-44	1482
F.45-54	688
F.55+	371
M.13-17	6393
M.18-24	7608
M.25-34	2086
M.35-44	1586
M.45-54	620
M.55+	361
U.18-24	124
U.25-34	28
U.35-44	83
U.45-54	65
U.UNKNOWN	26
Grand Total	43125

Best,

Re: Json like data to table?

rwunderlich — Thu, 19 Jul 2012 02:37:29 GMT

Here's a solution using subfield() and Generic load.

-Rob

Re: Json like data to table?

Thu, 19 Jul 2012 03:59:01 GMT

Hi Rob,

Thank you so much - your help is greatly appreciated. This is exactly what I needed.

Best regards,

piroglu1907