Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
In the [Flight Data] for the column Month use function 'Month' like
Month(Month) as Month
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
];
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)
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
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:
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
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
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.
Listboxes will only show distinct values. Can you post snapshot of your Month field values from qvd file.?