topic Re: using cross table and creating date fields in QlikView

using cross table and creating date fields

Thu, 09 Jul 2015 13:56:40 GMT

Hi I am using the cross table to convert 4 fields into 1 field as shown below.

CrossTable (FN,Value,5)

LOAD [ID],

[ Name],

[Data Type],

[Type],

[Date],

NIT_N_1_F1,

NIT_N_2_F1,

NIT_N_3_F1,

NIT_N_4_F1;

and it works but now I want the NIT_N_1_F1 shown as WeekDate i.e 7/10/2015 &

NIT_N_2_F1 as -7 i.e. 7/03/2015

NIT_N_3_F1 as -7 i.e. 6/26/2015

NIT_N_4_F1 as -7 i.e. 6/19/2015

NIT_N_106_F1 as -7 i.e. ********

CAN ANYONE HELP ME IN WRITIN THE QLIKVIEW SCRIPT.

Thanks in advance......................

Re: using cross table and creating date fields

robert_mika — Thu, 09 Jul 2015 14:09:19 GMT

Could you attached your source file?

Re: using cross table and creating date fields

Thu, 09 Jul 2015 17:57:08 GMT

cannot attach the source file

Re: using cross table and creating date fields

petter — Thu, 09 Jul 2015 20:04:23 GMT

You don't want to write the 106 lines manually?

Re: using cross table and creating date fields

Thu, 09 Jul 2015 20:08:10 GMT

yes i don't want to write.

and I write also i can not write them every week can we manage by using the variable in any way.

Re: using cross table and creating date fields

petter — Thu, 09 Jul 2015 20:11:44 GMT

So there will be one more column each week? But the number of columns will not increase indefinitely will they?

Re: using cross table and creating date fields

Thu, 09 Jul 2015 20:14:49 GMT

106 is constant but every week one week drops and one weeks adds on

Re: using cross table and creating date fields

petter — Thu, 09 Jul 2015 20:17:37 GMT

Then it is very easy to make a formula in Excel which increments the column number in the middle of the string by one and copying the formula down 105 times... copying the resulting 106 rows into your load script... it is done in less than two minutes ...

Re: using cross table and creating date fields

petter — Thu, 09 Jul 2015 20:20:25 GMT

NIT_N_1_F1,

NIT_N_2_F1,

NIT_N_3_F1,

NIT_N_4_F1,

NIT_N_5_F1,

NIT_N_6_F1,

NIT_N_7_F1,

NIT_N_8_F1,

NIT_N_9_F1,

NIT_N_10_F1,

NIT_N_11_F1,

NIT_N_12_F1,

NIT_N_13_F1,

NIT_N_14_F1,

NIT_N_15_F1,

NIT_N_16_F1,

NIT_N_17_F1,

NIT_N_18_F1,

NIT_N_19_F1,

NIT_N_20_F1,

NIT_N_21_F1,

NIT_N_22_F1,

NIT_N_23_F1,

NIT_N_24_F1,

NIT_N_25_F1,

NIT_N_26_F1,

NIT_N_27_F1,

NIT_N_28_F1,

NIT_N_29_F1,

NIT_N_30_F1,

NIT_N_31_F1,

NIT_N_32_F1,

NIT_N_33_F1,

NIT_N_34_F1,

NIT_N_35_F1,

NIT_N_36_F1,

NIT_N_37_F1,

NIT_N_38_F1,

NIT_N_39_F1,

NIT_N_40_F1,

NIT_N_41_F1,

NIT_N_42_F1,

NIT_N_43_F1,

NIT_N_44_F1,

NIT_N_45_F1,

NIT_N_46_F1,

NIT_N_47_F1,

NIT_N_48_F1,

NIT_N_49_F1,

NIT_N_50_F1,

NIT_N_51_F1,

NIT_N_52_F1,

NIT_N_53_F1,

NIT_N_54_F1,

NIT_N_55_F1,

NIT_N_56_F1,

NIT_N_57_F1,

NIT_N_58_F1,

NIT_N_59_F1,

NIT_N_60_F1,

NIT_N_61_F1,

NIT_N_62_F1,

NIT_N_63_F1,

NIT_N_64_F1,

NIT_N_65_F1,

NIT_N_66_F1,

NIT_N_67_F1,

NIT_N_68_F1,

NIT_N_69_F1,

NIT_N_70_F1,

NIT_N_71_F1,

NIT_N_72_F1,

NIT_N_73_F1,

NIT_N_74_F1,

NIT_N_75_F1,

NIT_N_76_F1,

NIT_N_77_F1,

NIT_N_78_F1,

NIT_N_79_F1,

NIT_N_80_F1,

NIT_N_81_F1,

NIT_N_82_F1,

NIT_N_83_F1,

NIT_N_84_F1,

NIT_N_85_F1,

NIT_N_86_F1,

NIT_N_87_F1,

NIT_N_88_F1,

NIT_N_89_F1,

NIT_N_90_F1,

NIT_N_91_F1,

NIT_N_92_F1,

NIT_N_93_F1,

NIT_N_94_F1,

NIT_N_95_F1,

NIT_N_96_F1,

NIT_N_97_F1,

NIT_N_98_F1,

NIT_N_99_F1,

NIT_N_100_F1,

NIT_N_101_F1,

NIT_N_102_F1,

NIT_N_103_F1,

NIT_N_104_F1,

NIT_N_105_F1,

NIT_N_106_F1

Re: using cross table and creating date fields

Thu, 09 Jul 2015 20:35:10 GMT

it works but now I want the NIT_N_1_F1 shown as WeekDate i.e 7/10/2015 &

NIT_N_2_F1 as -7 i.e. 7/03/2015

NIT_N_3_F1 as -7 i.e. 6/26/2015

NIT_N_4_F1 as -7 i.e. 6/19/2015

Re: using cross table and creating date fields

petter — Thu, 09 Jul 2015 20:37:32 GMT

You mean Weekday? Or do you mean the first day of a week?

Re: using cross table and creating date fields

Thu, 09 Jul 2015 20:42:15 GMT

weekday like end of the week every friday

Re: using cross table and creating date fields

petter — Thu, 09 Jul 2015 20:44:11 GMT

You could wrap each date with:

Date( WeekEnd( NIT_N_1_F1 ) - 2 ) AS NIT_N_1_F1,

Re: using cross table and creating date fields

petter — Thu, 09 Jul 2015 20:46:59 GMT

Date( WeekEnd( NIT_N_1_F1 ) ) AS NIT_N_1_F1,

Date( WeekEnd( NIT_N_2_F1 ) ) AS NIT_N_2_F1,

Date( WeekEnd( NIT_N_3_F1 ) ) AS NIT_N_3_F1,

Date( WeekEnd( NIT_N_4_F1 ) ) AS NIT_N_4_F1,

Date( WeekEnd( NIT_N_5_F1 ) ) AS NIT_N_5_F1,

Date( WeekEnd( NIT_N_6_F1 ) ) AS NIT_N_6_F1,

Date( WeekEnd( NIT_N_7_F1 ) ) AS NIT_N_7_F1,

Date( WeekEnd( NIT_N_8_F1 ) ) AS NIT_N_8_F1,

Date( WeekEnd( NIT_N_9_F1 ) ) AS NIT_N_9_F1,

Date( WeekEnd( NIT_N_10_F1 ) ) AS NIT_N_10_F1,

Date( WeekEnd( NIT_N_11_F1 ) ) AS NIT_N_11_F1,

Date( WeekEnd( NIT_N_12_F1 ) ) AS NIT_N_12_F1,

Date( WeekEnd( NIT_N_13_F1 ) ) AS NIT_N_13_F1,

Date( WeekEnd( NIT_N_14_F1 ) ) AS NIT_N_14_F1,

Date( WeekEnd( NIT_N_15_F1 ) ) AS NIT_N_15_F1,

Date( WeekEnd( NIT_N_16_F1 ) ) AS NIT_N_16_F1,

Date( WeekEnd( NIT_N_17_F1 ) ) AS NIT_N_17_F1,

Date( WeekEnd( NIT_N_18_F1 ) ) AS NIT_N_18_F1,

Date( WeekEnd( NIT_N_19_F1 ) ) AS NIT_N_19_F1,

Date( WeekEnd( NIT_N_20_F1 ) ) AS NIT_N_20_F1,

Date( WeekEnd( NIT_N_21_F1 ) ) AS NIT_N_21_F1,

Date( WeekEnd( NIT_N_22_F1 ) ) AS NIT_N_22_F1,

Date( WeekEnd( NIT_N_23_F1 ) ) AS NIT_N_23_F1,

Date( WeekEnd( NIT_N_24_F1 ) ) AS NIT_N_24_F1,

Date( WeekEnd( NIT_N_25_F1 ) ) AS NIT_N_25_F1,

Date( WeekEnd( NIT_N_26_F1 ) ) AS NIT_N_26_F1,

Date( WeekEnd( NIT_N_27_F1 ) ) AS NIT_N_27_F1,

Date( WeekEnd( NIT_N_28_F1 ) ) AS NIT_N_28_F1,

Date( WeekEnd( NIT_N_29_F1 ) ) AS NIT_N_29_F1,

Date( WeekEnd( NIT_N_30_F1 ) ) AS NIT_N_30_F1,

Date( WeekEnd( NIT_N_31_F1 ) ) AS NIT_N_31_F1,

Date( WeekEnd( NIT_N_32_F1 ) ) AS NIT_N_32_F1,

Date( WeekEnd( NIT_N_33_F1 ) ) AS NIT_N_33_F1,

Date( WeekEnd( NIT_N_34_F1 ) ) AS NIT_N_34_F1,

Date( WeekEnd( NIT_N_35_F1 ) ) AS NIT_N_35_F1,

Date( WeekEnd( NIT_N_36_F1 ) ) AS NIT_N_36_F1,

Date( WeekEnd( NIT_N_37_F1 ) ) AS NIT_N_37_F1,

Date( WeekEnd( NIT_N_38_F1 ) ) AS NIT_N_38_F1,

Date( WeekEnd( NIT_N_39_F1 ) ) AS NIT_N_39_F1,

Date( WeekEnd( NIT_N_40_F1 ) ) AS NIT_N_40_F1,

Date( WeekEnd( NIT_N_41_F1 ) ) AS NIT_N_41_F1,

Date( WeekEnd( NIT_N_42_F1 ) ) AS NIT_N_42_F1,

Date( WeekEnd( NIT_N_43_F1 ) ) AS NIT_N_43_F1,

Date( WeekEnd( NIT_N_44_F1 ) ) AS NIT_N_44_F1,

Date( WeekEnd( NIT_N_45_F1 ) ) AS NIT_N_45_F1,

Date( WeekEnd( NIT_N_46_F1 ) ) AS NIT_N_46_F1,

Date( WeekEnd( NIT_N_47_F1 ) ) AS NIT_N_47_F1,

Date( WeekEnd( NIT_N_48_F1 ) ) AS NIT_N_48_F1,

Date( WeekEnd( NIT_N_49_F1 ) ) AS NIT_N_49_F1,

Date( WeekEnd( NIT_N_50_F1 ) ) AS NIT_N_50_F1,

Date( WeekEnd( NIT_N_51_F1 ) ) AS NIT_N_51_F1,

Date( WeekEnd( NIT_N_52_F1 ) ) AS NIT_N_52_F1,

Date( WeekEnd( NIT_N_53_F1 ) ) AS NIT_N_53_F1,

Date( WeekEnd( NIT_N_54_F1 ) ) AS NIT_N_54_F1,

Date( WeekEnd( NIT_N_55_F1 ) ) AS NIT_N_55_F1,

Date( WeekEnd( NIT_N_56_F1 ) ) AS NIT_N_56_F1,

Date( WeekEnd( NIT_N_57_F1 ) ) AS NIT_N_57_F1,

Date( WeekEnd( NIT_N_58_F1 ) ) AS NIT_N_58_F1,

Date( WeekEnd( NIT_N_59_F1 ) ) AS NIT_N_59_F1,

Date( WeekEnd( NIT_N_60_F1 ) ) AS NIT_N_60_F1,

Date( WeekEnd( NIT_N_61_F1 ) ) AS NIT_N_61_F1,

Date( WeekEnd( NIT_N_62_F1 ) ) AS NIT_N_62_F1,

Date( WeekEnd( NIT_N_63_F1 ) ) AS NIT_N_63_F1,

Date( WeekEnd( NIT_N_64_F1 ) ) AS NIT_N_64_F1,

Date( WeekEnd( NIT_N_65_F1 ) ) AS NIT_N_65_F1,

Date( WeekEnd( NIT_N_66_F1 ) ) AS NIT_N_66_F1,

Date( WeekEnd( NIT_N_67_F1 ) ) AS NIT_N_67_F1,

Date( WeekEnd( NIT_N_68_F1 ) ) AS NIT_N_68_F1,

Date( WeekEnd( NIT_N_69_F1 ) ) AS NIT_N_69_F1,

Date( WeekEnd( NIT_N_70_F1 ) ) AS NIT_N_70_F1,

Date( WeekEnd( NIT_N_71_F1 ) ) AS NIT_N_71_F1,

Date( WeekEnd( NIT_N_72_F1 ) ) AS NIT_N_72_F1,

Date( WeekEnd( NIT_N_73_F1 ) ) AS NIT_N_73_F1,

Date( WeekEnd( NIT_N_74_F1 ) ) AS NIT_N_74_F1,

Date( WeekEnd( NIT_N_75_F1 ) ) AS NIT_N_75_F1,

Date( WeekEnd( NIT_N_76_F1 ) ) AS NIT_N_76_F1,

Date( WeekEnd( NIT_N_77_F1 ) ) AS NIT_N_77_F1,

Date( WeekEnd( NIT_N_78_F1 ) ) AS NIT_N_78_F1,

Date( WeekEnd( NIT_N_79_F1 ) ) AS NIT_N_79_F1,

Date( WeekEnd( NIT_N_80_F1 ) ) AS NIT_N_80_F1,

Date( WeekEnd( NIT_N_81_F1 ) ) AS NIT_N_81_F1,

Date( WeekEnd( NIT_N_82_F1 ) ) AS NIT_N_82_F1,

Date( WeekEnd( NIT_N_83_F1 ) ) AS NIT_N_83_F1,

Date( WeekEnd( NIT_N_84_F1 ) ) AS NIT_N_84_F1,

Date( WeekEnd( NIT_N_85_F1 ) ) AS NIT_N_85_F1,

Date( WeekEnd( NIT_N_86_F1 ) ) AS NIT_N_86_F1,

Date( WeekEnd( NIT_N_87_F1 ) ) AS NIT_N_87_F1,

Date( WeekEnd( NIT_N_88_F1 ) ) AS NIT_N_88_F1,

Date( WeekEnd( NIT_N_89_F1 ) ) AS NIT_N_89_F1,

Date( WeekEnd( NIT_N_90_F1 ) ) AS NIT_N_90_F1,

Date( WeekEnd( NIT_N_91_F1 ) ) AS NIT_N_91_F1,

Date( WeekEnd( NIT_N_92_F1 ) ) AS NIT_N_92_F1,

Date( WeekEnd( NIT_N_93_F1 ) ) AS NIT_N_93_F1,

Date( WeekEnd( NIT_N_94_F1 ) ) AS NIT_N_94_F1,

Date( WeekEnd( NIT_N_95_F1 ) ) AS NIT_N_95_F1,

Date( WeekEnd( NIT_N_96_F1 ) ) AS NIT_N_96_F1,

Date( WeekEnd( NIT_N_97_F1 ) ) AS NIT_N_97_F1,

Date( WeekEnd( NIT_N_98_F1 ) ) AS NIT_N_98_F1,

Date( WeekEnd( NIT_N_99_F1 ) ) AS NIT_N_99_F1,

Date( WeekEnd( NIT_N_100_F1 ) ) AS NIT_N_100_F1,

Date( WeekEnd( NIT_N_101_F1 ) ) AS NIT_N_101_F1,

Date( WeekEnd( NIT_N_102_F1 ) ) AS NIT_N_102_F1,

Date( WeekEnd( NIT_N_103_F1 ) ) AS NIT_N_103_F1,

Date( WeekEnd( NIT_N_104_F1 ) ) AS NIT_N_104_F1,

Date( WeekEnd( NIT_N_105_F1 ) ) AS NIT_N_105_F1,

Date( WeekEnd( NIT_N_106_F1 ) ) AS NIT_N_106_F1

Re: using cross table and creating date fields

Thu, 09 Jul 2015 20:49:18 GMT

there is no date in NIT_N_1_F1 this is field name

we need ti insert the date

NIT_N_1_F1 shown as WeekDate i.e 7/10/2015 &

NIT_N_2_F1 as -7 i.e. 7/03/2015

NIT_N_3_F1 as -7 i.e. 6/26/2015

NIT_N_4_F1 as -7 i.e. 6/19/2015

Re: using cross table and creating date fields

petter — Thu, 09 Jul 2015 20:52:04 GMT

By using CROSSTABLE it seemed that you where trying to load these dates from another table/source... So you want to just generate a table with no external source?

Re: using cross table and creating date fields

Fri, 10 Jul 2015 17:06:08 GMT

I need to generate the dates in app itself