topic Re: CrossTable -Lable Date in QlikView

CrossTable -Lable Date

priya945 — Sat, 16 Nov 2024 19:27:04 GMT

Hi All,

I have data in below cross table format.

Test:
Load * Inline [

Region, 1/1/2019,12/31/2018,11/30/2018
East, 1, 3, 2
West, 0, 1, 4
North, 3, 0, 1
South, 5, 2, 0
];

Could some one help me to get the data shown in below format( changing the date format)

Region	1-Jan-19	31-Dec-18	30-Nov-18
East	1	3	2
West	0	1	4
North	3	0	1
South	5	2	0

Thanks In advance.

Re: CrossTable -Lable Date

sunny_talwar — Mon, 16 Dec 2019 12:58:35 GMT

All you want is to format the date differently? Is that the goal here?

Re: CrossTable -Lable Date

Anil_Babu_Samineni — Mon, 16 Dec 2019 13:00:56 GMT

I don't see anything cross table needed here? Is that below table in Pivot table? If so, you need cross table. If you already done the cross table to make DateField may be use this in Pivot table

Date(Date#(DateField, 'MM/DD/YYYY'), 'DD-MMM-YYYY')

Re: CrossTable -Lable Date

priya945 — Mon, 16 Dec 2019 13:11:48 GMT

Hi Sunny,

Expected o/p is to use pivot table and formatting date as below

1-Jan-19

31-Dec-18

30-Nov-18

. PFA

Thanks

Re: CrossTable -Lable Date

priya945 — Mon, 16 Dec 2019 13:18:17 GMT

above is the expected o/p in pivot table with date formatting as below .

1-Jan-19

31-Dec-18

30-Nov-18

So we have to use the cross table and then format the date, which i am unable to figure it out. Please help me with the script to format the date after using cross table

Re: CrossTable -Lable Date

sunny_talwar — Mon, 16 Dec 2019 13:23:03 GMT

Try this

Test: CrossTable (Date, Value) LOAD * INLINE [ Region, 1/1/2019, 12/31/2018, 11/30/2018 East, 1, 3, 2 West, 0, 1, 4 North, 3, 0, 1 South, 5, 2, 0 ]; Table: NoConcatenate LOAD Region, Date(Date#(Date, 'M/D/YYYY'), 'DD-MMM-YY') as Date, Value Resident Test; DROP Table Test

Re: CrossTable -Lable Date

Anil_Babu_Samineni — Mon, 16 Dec 2019 13:23:53 GMT

Perhaps this?

CrossTable(DateField, Sales)
Load * Inline [

Region, 1/1/2019,12/31/2018,11/30/2018
East, 1, 3, 2
West, 0, 1, 4
North, 3, 0, 1
South, 5, 2, 0
];

Re: CrossTable -Lable Date

priya945 — Mon, 16 Dec 2019 13:33:28 GMT

Thanks sunny that worked. Some issue with my original data.

Re: CrossTable -Lable Date

priya945 — Tue, 17 Dec 2019 07:29:58 GMT

Hi Sunny,

After loading below data, there is no link between Date & RDate values. Could you please help to establish the connection between two columns.

Test2:

Load * Inline [

Region, CaseNumber, Date, 1/1/2019, 12/31/2018, 11/30/2018
East, 123, 1/1/2019, 1, 3, 2,
West, 234, 12/31/2018, 0, 1, 4,
North, 345 , 11/30/2018, 3, 0, 1,
South, 456, 10/31/2018, 5, 2, 0,

];

Test3:

CrossTable(RDate,Value,3)
Load * Resident Test2;

Thanks In advance.

Re: CrossTable -Lable Date

sunny_talwar — Tue, 17 Dec 2019 12:08:58 GMT

Can you elaborate what you mean that there is no connection between Date and RDate? What exactly is your goal?

Re: CrossTable -Lable Date

priya945 — Wed, 18 Dec 2019 07:27:18 GMT

Thanks sunny for coming back.

Suppose if i want to load below data only for last 2 month dates values , how to achieve this in script

Test2:

Load * Inline [

Region, CaseNumber, Date, 1/1/2019, 12/31/2018, 11/30/2018
East, 123, 1/1/2019, 1, 3, 2,
West, 234, 12/31/2018, 0, 1, 4,
North, 345 , 11/30/2018, 3, 0, 1,
South, 456, 10/31/2018, 5, 2, 0,

];

Test3:

CrossTable(RDate,Value,3)
Load * Resident Test2;

Re: CrossTable -Lable Date

sunny_talwar — Wed, 18 Dec 2019 12:36:01 GMT

Which two dates are you planning to load this for? Can you share how the data should look after it is loaded based on the sample provided?

Re: CrossTable -Lable Date

priya945 — Thu, 19 Dec 2019 06:12:44 GMT

Hi Sunny,

I want to load latest 2 dates as below. Could you please guide me to achieve this in the script

Region	North	South	West	East
Date	1-Jan-19	31-Dec-18	1-Jan-19	31-Dec-18	1-Jan-19	31-Dec-18	1-Jan-19	31-Dec-18
count of case Number						1	1
Count of Value

THanks In advance

Re: CrossTable -Lable Date

sunny_talwar — Thu, 19 Dec 2019 12:52:00 GMT

This?

Test2: LOAD * INLINE [ Region, CaseNumber, Date, 1/1/2019, 12/31/2018, 11/30/2018 East, 123, 1/1/2019, 1, 3, 2 West, 234, 12/31/2018, 0, 1, 4 North, 345, 11/30/2018, 3, 0, 1 South, 456, 10/31/2018, 5, 2, 0 ]; Test3: CrossTable(RDate, Value, 3) LOAD * Resident Test2; Test4: NoConcatenate LOAD Region, CaseNumber, Date, Date#(RDate, 'M/D/YYYY') as RDate, Value Resident Test3; DROP Table Test2, Test3; Temp: LOAD Region, CaseNumber, Date, Max(RDate) as RDate Resident Test4 Group By Region, CaseNumber, Date; Concatenate(Temp) LOAD Region, CaseNumber, Date, Max(RDate, 2) as RDate Resident Test4 Group By Region, CaseNumber, Date; Right Join (Test4) LOAD * Resident Temp; DROP Table Temp;