topic Re: Table Structure in QlikView

Table Structure

Fri, 15 Jun 2012 11:01:22 GMT

I have my table in this format with 12 months of data.

Dim_ID	Metric M1	Metric M2	Metric M3	Metric M4	Metric M5	Metric M6	Metric M7	Metric M8	Metric M9	Metric M10	Metric M11	Header 12
1	34	33	24	23	24	12	34	54	45	23	21	54
2	45	76	54	54	12	34	55	54	32	32	11	32
3	34	34	24	436	57	7	07	3	32	5	23	235
4	23	43	54	688	8	34	5	33	56	8	25	213
5	45	39	65	32	456	78	4	22	3	5	7	73
6	32	734	785	874	789	45	34	89	68	56	45	6
7	45	65	897	09	67	54	423	34	6	98	57	67
8	325	56	586	987	65	54	321	54	6	870	89	6
9	423	54	6	789	098	65	423	5	4	54	67	64
10	324	54	5	67	087	-98	34	463	87	75	523	324

I have to show chart for this with Date as dimension where M1 is Jan, M2 is Feb and so on...

Is there a way to do that. One way is transposing the columns and have each month data in one column and adding other column as Date Flag.

Dim_Id	Metric	Date Flag
1	34	Jan
2	32	Jan
3	23	Jan
4	65	Jan
5	33	Jan
1	45	Feb
2	65	Feb
3	23	Feb
4	44	Feb
5	55	Feb

But can i keep the same table structure and do that. coz transposing will increase my data size as Dim_Id will repeat for each month of data??

Re: Table Structure

jagannalla — Fri, 15 Jun 2012 11:54:05 GMT

Hello,

Please find the enclosed document. Hope it helps you.

Main:
LOAD RowNo() as Key,*;
LOAD * INLINE [
    M1, M2, M3, M4, M5, M6, M7, M8, M9, M10, M11, M12
    10, 20, 30, 40, 50, 60, 70, 80, 90, 100, 110, 120
    130, 140, 150, 160, 170, 180, 190, 200, 210, 220, 230, 240
];
Map1:
Mapping
LOAD 'M'&RowNo()as MonthNumber,MonthName;
LOAD * INLINE [
    MonthName
    Jan
    Feb
    Mar
    Apr
    May
    Jun
    Jul
    Aug
    Sep
    Oct
    Nov
    Dec
];
FOR i=1 to 12
Res_Main:
LOAD Key, M$(i) as Value,ApplyMap('Map1','M'&$(i)) as Month Resident Main;
NEXT

Regards,

Jagan

Re: Table Structure

Fri, 15 Jun 2012 11:59:39 GMT

Did you consider a crosstable-load?

That way, the data-entry can be kept in the structure of your first table, but Qlikview "reads" the data as displayed in your 2nd table.

See attached, hope it helps!

#edit

after reading Jagan's answer I realize that I may have mis-understood the requirement... The first table must be shown in the front-end? In that case, use Jagan's solution 🙂

Dim_ID	Metric M1	Metric M2	Metric M3	Metric M4	Metric M5	Metric M6	Metric M7	Metric M8	Metric M9	Metric M10	Metric M11	Header 12
1	34	33	24	23	24	12	34	54	45	23	21	54
2	45	76	54	54	12	34	55	54	32	32	11	32
3	34	34	24	436	57	7	07	3	32	5	23	235
4	23	43	54	688	8	34	5	33	56	8	25	213
5	45	39	65	32	456	78	4	22	3	5	7	73
6	32	734	785	874	789	45	34	89	68	56	45	6
7	45	65	897	09	67	54	423	34	6	98	57	67
8	325	56	586	987	65	54	321	54	6	870	89	6
9	423	54	6	789	098	65	423	5	4	54	67	64
10	324	54	5	67	087	-98	34	463	87	75	523	324

Dim_Id	Metric	Date Flag
1	34	Jan
2	32	Jan
3	23	Jan
4	65	Jan
5	33	Jan
1	45	Feb
2	65	Feb
3	23	Feb
4	44	Feb
5	55	Feb

Dim_ID	Metric M1	Metric M2	Metric M3	Metric M4	Metric M5	Metric M6	Metric M7	Metric M8	Metric M9	Metric M10	Metric M11	Header 12
1	34	33	24	23	24	12	34	54	45	23	21	54
2	45	76	54	54	12	34	55	54	32	32	11	32
3	34	34	24	436	57	7	07	3	32	5	23	235
4	23	43	54	688	8	34	5	33	56	8	25	213
5	45	39	65	32	456	78	4	22	3	5	7	73
6	32	734	785	874	789	45	34	89	68	56	45	6
7	45	65	897	09	67	54	423	34	6	98	57	67
8	325	56	586	987	65	54	321	54	6	870	89	6
9	423	54	6	789	098	65	423	5	4	54	67	64
10	324	54	5	67	087	-98	34	463	87	75	523	324

Dim_Id	Metric	Date Flag
1	34	Jan
2	32	Jan
3	23	Jan
4	65	Jan
5	33	Jan
1	45	Feb
2	65	Feb
3	23	Feb
4	44	Feb
5	55	Feb

Dim_ID	Metric M1	Metric M2	Metric M3	Metric M4	Metric M5	Metric M6	Metric M7	Metric M8	Metric M9	Metric M10	Metric M11	Header 12
1	34	33	24	23	24	12	34	54	45	23	21	54
2	45	76	54	54	12	34	55	54	32	32	11	32
3	34	34	24	436	57	7	07	3	32	5	23	235
4	23	43	54	688	8	34	5	33	56	8	25	213
5	45	39	65	32	456	78	4	22	3	5	7	73
6	32	734	785	874	789	45	34	89	68	56	45	6
7	45	65	897	09	67	54	423	34	6	98	57	67
8	325	56	586	987	65	54	321	54	6	870	89	6
9	423	54	6	789	098	65	423	5	4	54	67	64
10	324	54	5	67	087	-98	34	463	87	75	523	324

Dim_Id	Metric	Date Flag
1	34	Jan
2	32	Jan
3	23	Jan
4	65	Jan
5	33	Jan
1	45	Feb
2	65	Feb
3	23	Feb
4	44	Feb
5	55	Feb