topic Re: List box duplicated values in QlikView

List box duplicated values

Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I am learning Qlikview.

I don't know why my list box have duplicated it's values when I have the field is in common between two different table that from two different source.

Following is the code:

[Flight Data]:

LOAD [%Airline ID],

[%Carrier Group ID] as Carrier_Group_id,

[%Unique Carrier Code],

Month,

[From - To Airport Code],

[From - To Airport ID],

[From - To City],

[From - To State Code],

[From - To State]

FROM

[..\QVD-File\Data Files\QVDs\Flight Data.qvd]

(qvd)

.. < Other load statements>

LOAD * INLINE [

Month, MonthNum

Sep, 6

Jun, 3

Oct, 7

Nov, 8

Dec, 9

Jul, 4

Feb, 11

Mar, 12

Apr, 1

Jan, 10

May, 2

Aug, 5

];

The two table are joined automatically by Qlikview using the Month column.

Now when I create a list box with using the Month field I found that the same months appeared twice on the my list.

If I sort it by the load order following is my result following is my result:

Month
Sep
Jun
Oct
Nov
Dec
Jul
Feb
Mar
Apr
Jan
May
Aug
Sep
Jun
Oct
Nov
Dec
Jul
Feb
Mar
Apr
Jan
May
Aug

Also I noticed that the first 12 rows are on the right side while other is on the left side. Is it because they are under different data type? If so, should there be anyway I can fix it?

Thanks for your help!

Re: List box duplicated values

Sun, 01 May 2016 03:14:03 GMT

In the [Flight Data] for the column Month use function 'Month' like

Month(Month) as Month

Re: List box duplicated values

tamilarasu — Sun, 01 May 2016 03:52:28 GMT

Hi Elim,

Yes. One is in text format and another one is in number format. We can use Date#() function to interpret the text format into number. I guess, the number format is coming from qvd load. So, you can try changing the inline load like below,

Load Date#(Month,'MMM') as Month, MonthNum;

LOAD * INLINE [

Month, MonthNum

Sep, 6

Jun, 3

Oct, 7

Nov, 8

Dec, 9

Jul, 4

Feb, 11

Mar, 12

Apr, 1

Jan, 10

May, 2

Aug, 5

];

Re: List box duplicated values

Kushal_Chawda — Sun, 01 May 2016 05:12:01 GMT

Hi, another way to create the Month Number is

[Flight Data]:

LOAD [%Airline ID],

[%Carrier Group ID] as Carrier_Group_id,

[%Unique Carrier Code],

Month,

Pick(Match(Month,'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'),

10,11,12,1,2,3,4,5,6,7,8,9 ) as Monthnum,

[From - To Airport Code],

[From - To Airport ID],

[From - To City],

[From - To State Code],

[From - To State]

FROM

[..\QVD-File\Data Files\QVDs\Flight Data.qvd]

(qvd)

Re: List box duplicated values

Sun, 01 May 2016 12:13:40 GMT

Hi Tamil,

Thanks for your reply. However, I tried your solution and it just put everything to the right but haven't resolved the duplication.

Month
Sep
Jun
Oct
Nov
Dec
Jul
Feb
Mar
Apr
Jan
May
Aug
Sep
Jun
Oct
Nov
Dec
Jul
Feb
Mar
Apr
Jan
May
Aug

Elim

Re: List box duplicated values

trdandamudi — Sun, 01 May 2016 12:23:41 GMT

Are you sure you doing the right thing ? I just copied Tamil's code and created a list box and everything is fine. I don't see the word "Month" in the list. See attached file:

Re: List box duplicated values

Sun, 01 May 2016 12:30:20 GMT

HI Kushal,

Thanks for your reply. I have actually used left join to fix the problem. But I actually want to clear my understanding.on using list box. Because, if both month is the same, than it should be in the same cell in the list box. But It is now in a different cell which have confused me.

Elim

Re: List box duplicated values

Sun, 01 May 2016 12:51:18 GMT

Hi Thirumala,

Yes you are right, I have done something wrongly. So I have just removed my original reply. But it doesn't resolve my issue. I am expecting the same result as what you have. But not from one table, it should be a from two table and I can't understand why Qlikview cannot join the inline table to the Flight Data table when it load the inline table.

Elim

Re: List box duplicated values

trdandamudi — Sun, 01 May 2016 13:04:37 GMT

Ok. Going forward please don't remove any replies, because the flow will be missing and other people who are looking at it will be confused.

Now back to your issue... Can you please post a sample file clearly explaining the issue you have and the output you are expecting. Someone in the community will get back to you with possible solutions.

Re: List box duplicated values

tamilarasu — Sun, 01 May 2016 13:10:18 GMT

Listboxes will only show distinct values. Can you post snapshot of your Month field values from qvd file.?

Re: List box duplicated values

MarcoWedel — Sun, 01 May 2016 17:37:06 GMT

Try loading your Month text values like:

Month(Date#(Month,'MMM')) as Month

Dual(Month,Month(Date#(Month,'MMM'))) as Month

hope this helps

regards

Marco

Re: List box duplicated values

Sun, 08 May 2016 00:59:10 GMT

Hi Tamil,

Don't know what's happening but it suddenly disappeared. In this three days I have only reinstalled my Virtual Machine and upgraded from Qlikview 11 to Qlikview 12 and recreate the same file and this problem suddenly gone.

Funny!

Elim

Re: List box duplicated values

Sun, 08 May 2016 01:13:47 GMT

Hi Tamil,

I know why my problem disappeared now. It is because I have added a left join statement on the inline view by mistake. Once I removed the left join statement, the problem reappeared.

Attached is my qlikview file. Could you please help me to have a look.

The month field is available from two different tables:

[Flight Data]: in the Main Data Sheet

[month_fiscal_year]:: in the Inline sheet.

Thanks in advance!

Kind Regards,

Elim

Re: List box duplicated values

Sun, 08 May 2016 01:36:32 GMT

Macro,

Your way work! But do you have any idea why Date#(Month,'MMM') not equal to Month(Date#(Month,'MMM'))?

Thanks!

Elim

Re: List box duplicated values

Anonymous — Sun, 08 May 2016 04:46:57 GMT

may be to create the calendar than slove problem