Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

List box duplicated values

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!

1 Solution

Accepted Solutions
MarcoWedel

Try loading your Month text values like:

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

or

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

hope this helps

regards

Marco

View solution in original post

14 Replies
Not applicable
Author

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

Month(Month) as Month

tamilarasu
Champion
Champion

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

];

Kushal_Chawda

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)

Not applicable
Author

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

trdandamudi
Master II
Master II

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:

Not applicable
Author

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

Not applicable
Author

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

trdandamudi
Master II
Master II

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.

tamilarasu
Champion
Champion

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